| Author |
Topic |
|
hari_sql
Starting Member
22 Posts |
 Posted - 2010-03-25 : 06:34:13
|
| Hi My current DB Transaction log size (MS SQL Server 2005) is around 45GB.I tried to shrink the T-log using DBCC SHRINKFILE (N'EmpDB_log' , 1). But the size of the T-log is not getting reduced. I tried the same from Management Studio interface also, but the same result. I have data being written continuously in to the DB, while the shrink operation is being performed. Can anyone please respond with why the size of the T-log is not getting reduced?!Thanks and Regards,Hari. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-25 : 06:55:12
|
| You need to check the recovery model of the database (in database properties). If the database is in Full- or bulk logged recovery model you need to take a transaction log backup before the logfile can be shrunk. If the recovery model is simple your log shouldn't keep growing unless you have some really large batch transactions goin or something. In that case you need to stop these transactions and then run a shrink on the file. Please note that shrinking database files is generally considered bad practice and should be avoided unless something extraordinary has happened.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
 |
|
|
hari_sql
Starting Member
22 Posts |
Posted - 2010-03-25 : 07:43:36
|
| Dear LumbagoIt is Full logged recovery. There is mirroring and log shipping enabled. But shrinking is done after the mirroring and log shipping are disabled. But still the T-log size is not shrinking!Thanks & Regards,Hari. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-25 : 07:50:06
|
| Do you have any open long-running transactions that might be blocking the database from setting a checkpoint in the log? When was your last transaction log backup?- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
hari_sql
Starting Member
22 Posts |
Posted - 2010-03-25 : 08:33:03
|
| Dear LumbagoBulk data is being written into the DB every minute.And its been around 3 months since the last T-log backup was done.Thanks & Regards,Hari. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-25 : 08:48:26
|
| Well...there you have it then. Do a t-log backup and then shrink the file. As I said in my initial post: " If the database is in Full- or bulk logged recovery model you need to take a transaction log backup before the logfile can be shrunk"- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-25 : 09:20:13
|
| "log shipping enabled" ... "its been around 3 months since the last T-log backup was done"Doesn't sound quite right ?? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-25 : 09:33:36
|
| Totally doesn't make sense but I assumed log shipping was disabled on purpose so I didn't make any comments about it: "But shrinking is done after the mirroring and log shipping are disabled"- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-25 : 09:38:30
|
| Yup, saw that, but read it that they were stopped just for the duration of trying to shrink the file.If they have been stopped for 3+ months then they are not in use, eh?!! |
|
|
|
hari_sql
Starting Member
22 Posts |
Posted - 2010-03-26 : 07:24:49
|
| Guys,Thank you for the inputs.I was mentioned about a physical backup (copy/paste) of the T-log to the same system in the near future as part of trying out shrinking.Regards,Hari. |
|
|
|
|
DBCC SHRINKFILE : T-log