SQL Server Performance

Diskfull98% -can shrink TempDB?- emergency

Discussion in 'General DBA Questions' started by gkrishn, Feb 11, 2005.

  1. gkrishn New Member

    Hi,

    my E: drive is full .it as only one folder D:mssql7Data which contain...
    *11 database data files

    *tempDb datafile which is 2GB

    Y like tht?? is it normal? can i do somthin wiht TempDb datafile to free space than touchin other data files?? please advice . m in an emergency sitution now .

    Thnx in advance.

  2. satya Moderator

    You must think to shrink any of the databases or add more space to the E: drive.

    If your application is using lot of queris to handle in TEMPDB then you may get into the issues if you shrink the TEMPDB.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. gkrishn New Member

    Adding more space not possible here.

    1)I have to think abt shrinkin any datafiles.

    whts the procedure?do i need to tak DB offline? can i just right click and give shrink

    2)i have to create another Datafile in other drive and hav to move some tables in one DB to it.
    is tht possible??? i hav to try this if shrinkin not works .

    Please advice. I hav no time to explore much.Hv to start from where u say .

    Thnks in advance .
  4. mmarovic Active Member

    quote:i have to create another Datafile in other drive and hav to move some tables in one DB to it.
    is tht possible??? i hav to try this if shrinkin not works .
    Yes it is. It is better solution. You move tables to new file group by (re)creating clustered indexes on that filegroup.
  5. satya Moderator

    You can use ENterprise Manager to shrink the database(s) or DBCC SHRINKDATABASE to utilise the statement, books online is your best pal in this regard. ANd during the shrink process the users may get slow responses and before doing this process best to backup the databases.

    Refer to thishttp://www.databasejournal.com/features/mssql/article.php/3339681 link to monitor the database growth for a certain period to avoid such situation in future.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. gkrishn New Member

    ok First i wil do a shrink and wil get back to you .<br /><br />abt creating a new file group and clusterd index, m not familiar <img src='/community/emoticons/emotion-6.gif' alt=':(' /> .i think creatin index and al belongs to developers. sugest any good articles or pls reply me in details<br /><br />THnx
  7. satya Moderator

    http://www.sqljunkies.com/How%20To/B9F7F302-964A-4825-9246-6143A8681900.scuk explains you the scenario to move the files.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. gkrishn New Member

    Sathya/all

    I shrunk two databases and now i got 2 GB extra free space.was so surprised to see this.

    I think al the space pbms, shrinkin is the best solution to try first .

    THnx
  9. satya Moderator

    Always not, if you're performing DBCC DBREINDEX process and next moment with SHRINK then it will have negative effect and it will be an intermim solution until you get more disks out to addup more space to the data files drive.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. gkrishn New Member

    Sathya tht was a valuble info for me.<br /><br />i hav shrunk one more DB ther i got 1 more GB free to OS . i feel like doin same to all 11 databases <img src='/community/emoticons/emotion-1.gif' alt=':)' /> .Does it hav any negative impact? . <br /><br />i dont hav any jobs runnin in my server. even backups are manual only .so can i go for shrinkin al databases. any negative impact settin auto shrink to True . please wordout ur experience here .<br /><br />thnx
  11. mmarovic Active Member

    Yes, the most used bad practice is to allow autoshrink especailly on prod server. Shrinking increses internal fragmentation. It also affects external (filesystem) fragmentation because shrinked db usually has to expanded again soon after shrink. It is waste of resources. It is better to set fixed size of db and take care to maintain db file size by regular transaction log backups and archiving process in place. Also regular index defragmantation helps keeping desirable file size and query performance.
  12. satya Moderator

    GKrishn

    YOur ultimate action to overcome this situation is to buy more disks and addup to the server and as an interim solution you can depend on the shrink process, and you may lead into performance issue from applications if they are using resource intensive queries and DBCC DBREINDEX will help to reduce this part of fragmentation, but will contribute to the space issue again by adding more space to the database.

    As suggested do not use AUTO SHRINK option on the production databases.

    IF possible think about archiving data from possible databases.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. gkrishn New Member

    Hi

    yea i will follow as per ur said .

    Mine is sql server 7 .There is no recovery model available in this version.
    I have daily(manual) one full backup and 5 transactional backup for datbase PRODUCT .
    This will truncate the log automaticlly?or i need to truncate the log daily before takin full backup ???

    i know in sql server 2000, if R-model is FUll, backinup wil trucate the log automatically,not sure abt this version. any idea?
  14. mmarovic Active Member

    If you have truncate log on checkpoint set, this is pretty much equivalent to mssql server 2000 simple recovery model. Otherwise it works like mssql server 2000 full recovery model.
  15. gkrishn New Member

    Hi. i understood the concept . but see the following case.

    my logile (productlog.ldf) is 1.7GB .As i told b4...I have daily(manual) one full backup and 5 transactional backup for datbase PRODUCT .but stil then Y my trans log is of this much size .As u told it shud truncate on each tranlog backup , right? truncate on check point is set to FALSE .

    my translogbackups are of size 240MB,175MB etc...

    Pls advice


    Rajiv
    SQL-DBA
  16. satya Moderator

    In versions SQL 7 & 2000 Transaction log truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. For information on shrinking the size of a physical log file, see Shrinking the Transaction Log.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  17. gkrishn New Member

    Oh <img src='/community/emoticons/emotion-1.gif' alt=':)' />).i was misunderstood abt it . THnx Sathya .<br /><br />I was meant to reduce Transactional log size.i think there is no way to do tht reduced.<br /><br /><br /><br />Rajiv<br />SQL-DBA
  18. satya Moderator

    Then you must use DBCC SHRINKFILE to shrink the physical size as mentioned.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  19. gkrishn New Member

    i shrunk DB already.I think there is no use shrinkin Log again after this, right ?


  20. satya Moderator

    True and monitor the log growth on all the databases and until you get more disks try to shrink the log rather than the databases.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  21. gkrishn New Member

    But sathya see, my log is of 1.7GB and logbackups are of 240MB,175MB etc...

    As i told u, i shrunk database 2 day before.then hw come this much of difference is comin. 1.7GB - 240MB



    Rajiv
    SQL-DBA
  22. satya Moderator

    SQL Server segments each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. SQL Server chooses the size of the virtual log files dynamically while creating or extending log files. SQL Server tries to maintain a small number of virtual files.

    The active portion of the log will be truncated when the BACKUP LOG statement is generated or any checkpoint occurs.

    If you have the database maintenance plans scheduled over the weekend then the log size will be increased to cater the scheduled jobs. You can shrink the 1.7gb Transaction log to 1GB and monitor the log growth using DBCC SQLPERF(LOGSPACE).



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  23. gkrishn New Member

    Ok thnx sathya/All for ur info. I hope this loop messages will be usefull to other also .

    Best regards,

    Rajiv

Share This Page