shrink database doubts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

shrink database doubts

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-22 : 01:05:02

Dear All,
i've a database logfile with initial size of 117 Gb. which is a static database, and around 1 Gb data will be there in the database. i'd like to bring the database logfile to 10Mb initial size.
how can i proceed now?
1)dbcc shrinkfile or DBCC shrinkdatabase?
2)will it goes to any dataloss?
3)can i handle that in peaktime? (ofcource this Database is static no changes. but it contains the master data which is used in applications)

please guide me to handle this task

Arnav
Even you learn 1%, Learn it with 100% confidence.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-22 : 02:32:14

Why did the log get that big in the first place? If you don't know that, it's likely that the log will get that big again.

1) shrinkfile, unless you want to really fragment your indexes.
2) no
3) You can. It won't help the server's performance if you do, but there's nothing stopping you from doing it.

--
Gail Shaw
SQL Server MVP

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-22 : 04:02:41

Thank you gila master, can you please tell me how can i make my 117 Gb log file as 10Mb Log file? can you please provide me the syntax....

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-22 : 04:42:09

can i use this script?
USE mydb
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE mydb
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (mydb_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE mydb
SET RECOVERY FULL;
GO

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-22 : 04:54:09

but the logfile is not setting to 1 Mb. it is going to take the 10% of initial size.

Arnav
Even you learn 1%, Learn it with 100% confidence.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-22 : 08:26:38

You can use that script. Are you aware of what that's going to do to your recovery chain? May I suggest you carefully read through the following sections in Books Online
"Considerations for Switching from the Full or Bulk-Logged Recovery Model"
"Considerations for Switching from the Simple Recovery Model"
"Shrinking the Transaction Log"
"Working with Transaction Log Backups"

If you don't want to shrink to 1 MB, then change the appropriate parameter to the shrink file. See the section in Books Online on DBCC Shrink file.

Have you found out why the log file's that big in the first place?

--
Gail Shaw
SQL Server MVP

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-12-22 : 10:12:26

Would you not also look at regular tran log backups

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-22 : 10:32:41

quote:
Originally posted by NeilG

Would you not also look at regular tran log backups

If they're not already been done, absolutely.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources