locked
Backup of SQL2K db to network pc but no space in servers c: drive RRS feed

  • Question

  • Friends,

    OS: windows 2003 sp1
    DB: sql server 2000 sp3

    the sql server is installed in C: drive.
    Now the C: drive is full (sometimes it going to 0bytes but currently 3 mb free). i cannot reduce any space in c: drive. So that backup is also not working.
    Now i would like to take a backup in the network pc.
    Can i take a backup of the db while the users are connected to the database?
    Now our users(15 users) are all connected to the database and they are working.
    if i take a backup, will it cause any problem to the server or will there be any disconnection during this backup.
    and moreover, what is the best way to get this data to a new server. now we have a new server without this database. only sql server 2000 is installed.


    now the free space in the c: drive is 0 bytes.
    now the users are also connected and they are entering some records...how its storing when the c: driver is having 0 bytes. 
    how can i check that remaining free space size?
    Also, can i take a backup in this situation?


    thanks and waiting for somebody's help.
    Monday, May 20, 2013 4:31 AM

Answers

  • Friends,

    OS: windows 2003 sp1
    DB: sql server 2000 sp3

    the sql server is installed in C: drive.

    ans:which Drive holds your database files (mdf and ldf files).?  I believe its not in your c drive since no space in disk will make your databases suspect mode.

    So that backup is also not working.

    ans:Try to take backup to another drive.

    Can i take a backup of the db while the users are connected to the database?

    ans:Yes, Backup will not block your users's read or write activities.

    Now our users(15 users) are all connected to the database and they are working.
    if i take a backup, will it cause any problem to the server or will there be any disconnection during this backup.

    ans:No



    Srinivasan


    Monday, May 20, 2013 4:50 AM
  • how can i check that remaining free space size?

    +++++++++++++

    Just run the  below query to check the free space in your databases:

    create table #tempdbsize 
    (
    Servername varchar(100),
    DB_Name Varchar(50),
    Logical_Filename varchar(100),
     Physical_Filename varchar(1000),
     FileSizeMB Varchar (50),
     SpaceUsedMB Varchar(50),
     FreeSpaceMB varchar(50))
    
    Insert into #tempdbsize 
    exec sp_MSforeachdb ' use [?] select @@Servername,
    	db_name(),
          name
        , filename
        , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
        , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
        , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a'
    
    select distinct * from #tempdbsize
    
    drop table #tempdbsize
    

    Might be your database is having free space that's why user's are able to perform the actions.

    • Marked as answer by sathyguy1 Friday, May 24, 2013 5:37 AM
    Wednesday, May 22, 2013 1:13 AM

All replies

  • Friends,

    OS: windows 2003 sp1
    DB: sql server 2000 sp3

    the sql server is installed in C: drive.

    ans:which Drive holds your database files (mdf and ldf files).?  I believe its not in your c drive since no space in disk will make your databases suspect mode.

    So that backup is also not working.

    ans:Try to take backup to another drive.

    Can i take a backup of the db while the users are connected to the database?

    ans:Yes, Backup will not block your users's read or write activities.

    Now our users(15 users) are all connected to the database and they are working.
    if i take a backup, will it cause any problem to the server or will there be any disconnection during this backup.

    ans:No



    Srinivasan


    Monday, May 20, 2013 4:50 AM
  • thanks for your reply.

    ================

    ans:which Drive holds your database files (mdf and ldf files).?  I believe its not in your c drive since no space in disk will make your databases suspect mode.

    So that backup is also not working.

    ================

    C: drive only holds the mdf and ldf...thats what i am wondering....what will happen to the data and the database in this kind of situation. but still the users are able to connect the db and able to insert new records.

    Monday, May 20, 2013 4:53 AM
  • how can i check that remaining free space size?

    +++++++++++++

    Just run the  below query to check the free space in your databases:

    create table #tempdbsize 
    (
    Servername varchar(100),
    DB_Name Varchar(50),
    Logical_Filename varchar(100),
     Physical_Filename varchar(1000),
     FileSizeMB Varchar (50),
     SpaceUsedMB Varchar(50),
     FreeSpaceMB varchar(50))
    
    Insert into #tempdbsize 
    exec sp_MSforeachdb ' use [?] select @@Servername,
    	db_name(),
          name
        , filename
        , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
        , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
        , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a'
    
    select distinct * from #tempdbsize
    
    drop table #tempdbsize
    

    Might be your database is having free space that's why user's are able to perform the actions.

    • Marked as answer by sathyguy1 Friday, May 24, 2013 5:37 AM
    Wednesday, May 22, 2013 1:13 AM
  • Apart from what Ram is suggesting

    two points

    1.You are using sql server 2000 SP3 please upgrade to SP4 ASAP.

    2.It would be better if you start using newer versions of sql server(2008/2008R2/2012) may be SQL 2005.


    Soldier..Sir we are surrounded from all sides by enemy.. Major: Good, we can attack in any direction Thats attitude.. Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, May 22, 2013 6:17 AM