DB Newbie question ahead.
I have a mere 100MB db with a 4GB transaction log. I want to truncate the log as I understand that truncating it will shrink the log by removing the transactions that have already taken place. However, the option to do a transaction backup is greyed out. I suspect this is from the db being in transactional replication with another server; however, I don't know for sure.
Are there any other ways that I can shrink the transaction log? I would like to do shrink it without taking the db offline either.I think the recovery model was set to simple, that's why you can't take log backup. You can use DBCC Shrinkfile to shrink your log file to the size you want.|||Are you using SQL 2000?
If so you can do that by the following
- Go to DB properties and change the backup type to FULL
- Take a backup of the database and keep it safe.
- Go to DB properties again change the backup type to SIMPLE
- Go to all tasks and Shrink the file
- Go to DB properties again and change the backup type to FULL again so that it logs everything.|||Yes, I am using SQL2000. That's important information ;)
SQL2000 on win2003.
smasanam: I tried that and it didn't shrink the log file. Still 4.5GB.
Is the log file important for transactional replication? This db is replicated nightly to another server. If everything is updated and fully bkup'd would the log file be necessary? Or could I just delete it?
Thanks for the help.
Showing posts with label truncate. Show all posts
Showing posts with label truncate. Show all posts
Thursday, March 8, 2012
Cannot Truncate Transaction Log!
My Transaction Log on a production database has grown to 5 GB. And
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!
Labels:
database,
dbcc,
following,
grown,
log,
microsoft,
mysql,
opentran,
oracle,
production,
replicated,
server,
sql,
transaction,
truncate
Cannot Truncate Transaction Log!
My Transaction Log on a production database has grown to 5 GB. And
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!|||Unfortunately, the log cannot be shrunk, because shrinking only clears
up free space on the log, and what I have is something that is showing
up as used space. This makes it impossible to shrink, until I've
unflagged this transaction.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Laura
Run DBCC Loginfo(db_id)
There is a column is called 'status' .If you see the value=2 of this column
at the end ,so you have running(active) transaction and therefore you will
not able to shrink the log.
Run dummy inserts to move the status=2 on the top of the table.
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:uo$gJGgAEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Unfortunately, the log cannot be shrunk, because shrinking only clears
> up free space on the log, and what I have is something that is showing
> up as used space. This makes it impossible to shrink, until I've
> unflagged this transaction.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!|||Unfortunately, the log cannot be shrunk, because shrinking only clears
up free space on the log, and what I have is something that is showing
up as used space. This makes it impossible to shrink, until I've
unflagged this transaction.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Laura
Run DBCC Loginfo(db_id)
There is a column is called 'status' .If you see the value=2 of this column
at the end ,so you have running(active) transaction and therefore you will
not able to shrink the log.
Run dummy inserts to move the status=2 on the top of the table.
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:uo$gJGgAEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Unfortunately, the log cannot be shrunk, because shrinking only clears
> up free space on the log, and what I have is something that is showing
> up as used space. This makes it impossible to shrink, until I've
> unflagged this transaction.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
Labels:
andall,
database,
dbcc,
grown,
log,
microsoft,
mysql,
opentran,
oracle,
production,
server,
sql,
thefollowingreplicated,
transaction,
truncate
Cannot truncate table because it is being referenced by another ob
I recieve the following error after configuring replication between two servers. I am not sure how to resolve it. Does anyone have any ideas?
Thanks in advance,
Peter
Cannot TRUNCATE TABLE 'TwAccType' because it is being referenced by object 'TwAccTypeUserNotNull'.
(Source: TUMIME2 (Data source); Error number: 3729)
Event Type:Information
Event Source:SQLSERVERAGENT
Event Category:Job Engine
Event ID:203
Date:6/15/2004
Time:10:42:06 AM
User:N/A
Computer:TUMIME1
Description:
SubSystem Message - Job 'TUMIME1-IME-TUMIME2-2' (0x73197FC085876B4BB39379788ECBA652), step 2 - Cannot TRUNCATE TABLE 'TwAccType' because it is being referenced by object 'TwAccTypeUserNotNull'.
Thanks Paul-
I will get back to you.
"Paul Ibison" wrote:
> Truncate Table can't be done if the table has FKs
> depending on it. If you look on the article properties,
> snapshot tab there is a naming conflicts section. Is yours
> set to 'Delete all data in existing table?'. If so then
> this is the cause of your error. You could drop the
> constraint in a pre-script and add it back if nesessary in
> a postscript. These options are available on the
> Publication Properties, Snapshot Tab.
> HTH,
> Paul Ibison
>
Thanks in advance,
Peter
Cannot TRUNCATE TABLE 'TwAccType' because it is being referenced by object 'TwAccTypeUserNotNull'.
(Source: TUMIME2 (Data source); Error number: 3729)
Event Type:Information
Event Source:SQLSERVERAGENT
Event Category:Job Engine
Event ID:203
Date:6/15/2004
Time:10:42:06 AM
User:N/A
Computer:TUMIME1
Description:
SubSystem Message - Job 'TUMIME1-IME-TUMIME2-2' (0x73197FC085876B4BB39379788ECBA652), step 2 - Cannot TRUNCATE TABLE 'TwAccType' because it is being referenced by object 'TwAccTypeUserNotNull'.
Thanks Paul-
I will get back to you.
"Paul Ibison" wrote:
> Truncate Table can't be done if the table has FKs
> depending on it. If you look on the article properties,
> snapshot tab there is a naming conflicts section. Is yours
> set to 'Delete all data in existing table?'. If so then
> this is the cause of your error. You could drop the
> constraint in a pre-script and add it back if nesessary in
> a postscript. These options are available on the
> Publication Properties, Snapshot Tab.
> HTH,
> Paul Ibison
>
Labels:
configuring,
database,
error,
following,
ideasthanks,
microsoft,
mysql,
oracle,
recieve,
referenced,
replication,
resolve,
server,
servers,
sql,
table,
truncate
Cannot truncate logfile in SQL server 2000
Nilkanth Desai
nsdesai@.tis-world.com
I am using SQL Server 2000. In this case database size is 750 MB and since
last 10 days its logfile is constantly increasing its size. Since last 2 day
s
it has crossed 14GB. Thses logfiles are having extension of .ldf. I have
tried shrink database option from Enterprise manager and used DBCC from quer
y
analyser. But this logfile did not truncated.
Can anyone tell me solution for this problem.8f84bacebad4b881" target="_blank">http://groups.google.com.au/group/m...f84bacebad4b881
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Nilkanth Desai" <Nilkanth Desai@.discussions.microsoft.com> wrote in message
news:D70F6F79-84F6-4F9F-B514-885FB86D8B18@.microsoft.com...
> Nilkanth Desai
> nsdesai@.tis-world.com
> I am using SQL Server 2000. In this case database size is 750 MB and since
> last 10 days its logfile is constantly increasing its size. Since last 2
> days
> it has crossed 14GB. Thses logfiles are having extension of .ldf. I have
> tried shrink database option from Enterprise manager and used DBCC from
> query
> analyser. But this logfile did not truncated.
> Can anyone tell me solution for this problem.
nsdesai@.tis-world.com
I am using SQL Server 2000. In this case database size is 750 MB and since
last 10 days its logfile is constantly increasing its size. Since last 2 day
s
it has crossed 14GB. Thses logfiles are having extension of .ldf. I have
tried shrink database option from Enterprise manager and used DBCC from quer
y
analyser. But this logfile did not truncated.
Can anyone tell me solution for this problem.8f84bacebad4b881" target="_blank">http://groups.google.com.au/group/m...f84bacebad4b881
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Nilkanth Desai" <Nilkanth Desai@.discussions.microsoft.com> wrote in message
news:D70F6F79-84F6-4F9F-B514-885FB86D8B18@.microsoft.com...
> Nilkanth Desai
> nsdesai@.tis-world.com
> I am using SQL Server 2000. In this case database size is 750 MB and since
> last 10 days its logfile is constantly increasing its size. Since last 2
> days
> it has crossed 14GB. Thses logfiles are having extension of .ldf. I have
> tried shrink database option from Enterprise manager and used DBCC from
> query
> analyser. But this logfile did not truncated.
> Can anyone tell me solution for this problem.
Cannot Truncate due to Fks
i read that i can Truncate a table even if child table has no records so i tries to disable constraints but still can't get it to work
Cannot truncate table 'InventoryPC' because it is being referenced by a FOREIGN KEY constraint.
disabling code on lines 9-13 and enabling codes on 36-40
1ALTER PROCEDURE dbo.RevertDB23/* Reverts Database to original "Clean" State */4AS5SET NOCOUNT OFF6DECLARE @.LogAS varchar(MAX), @.RowsInDBAS int7SET @.Log ='RevertDB Started at ' +CAST(GETDATE()AS varchar(50)) +'\n'89/* *** Disable Constraints *** */10ALTER TABLE BookingNOCHECK CONSTRAINTALL11ALTER TABLE InventoryPCNOCHECK CONSTRAINTALL12ALTER TABLE PCNOCHECK CONSTRAINTALL13ALTER TABLE PlatformNOCHECK CONSTRAINTALL1415/* *** Start Truncates *** */16TRUNCATE TABLE Booking17SET @.Log = @.Log +'Trucate Table Booking - Done' +'\n'18SET @.RowsInDB = (SELECTCOUNT(BookingID)FROM Booking)19SET @.Log = @.Log +'-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'2021TRUNCATE TABLE InventoryPC22SET @.Log = @.Log + 'TrucateTable InventoryPC - Done' + '\n'23SET @.RowsInDB = (SELECT COUNT(InventoryID) FROM InventoryPC)24SET @.Log = @.Log + '-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'2526TRUNCATE TABLE PC27SET @.Log = @.Log +'Trucate Table PC - Done' +'\n'28SET @.RowsInDB = (SELECTCOUNT(PCID)FROM PC)29SET @.Log = @.Log +'-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'3031TRUNCATE TABLE Platform32SET @.Log = @.Log + 'TrucateTable Platform - Done' + '\n'33SET @.RowsInDB = (SELECT COUNT(PlatformID) FROM Platform)34SET @.Log = @.Log + '-- Rows Affected: ' + CAST(@.@.ROWCOUNT AS varchar) + ', Rows in Table: ' + CAST(@.RowsInDB AS varchar(10)) + '\n'3536/* *** Enable Constraints *** */37ALTER TABLE BookingWITH CHECK CHECK CONSTRAINTALL38ALTER TABLE InventoryPCWITH CHECK CHECK CONSTRAINTALL39ALTER TABLE PCWITH CHECK CHECK CONSTRAINTALL40ALTER TABLE PlatformWITH CHECK CHECK CONSTRAINTALL4142SET @.Log = @.Log +'*** End Truncates ***' +'\n'43/* *** End Truncates *** */4445/* *** Start Insert Platform *** */46SET @.Log = @.Log +'Start Insert Platform' +'\n'4748EXEC dbo.InsertPlatform'Windows XP SP2 Professional Edition','Some description for Windows XP SP2 Professional Edition over here …'49EXEC dbo.InsertPlatform'Windows Vista Ultimate','See everything you''re working on more clearly with Windows Aero, and quickly switch between windows or tasks using Windows Flip 3D and Live Thumbnails. You can easily find what you need—when you need it―with Instant Search and live icon previews that display the actual contents of your files. And while you''re at it, give your personal productivity a boost with instant access to the information you care about using Windows Sidebar and Gadgets. Put these easy-to-use and customizable mini-applications on your desktop and reveal the information you''re looking for at a glance.\nWebsite: http://www.microsoft.com/windows/products/windowsvista/seeit/default.mspx'50EXEC dbo.InsertPlatform'Apple Mac OS X Tiger','Some description for Apple Mac OS X Tiger over here …'51EXEC dbo.InsertPlatform'Apple Mac OS X Leopard','Desktop: The new look of Leopard showcases your favorite desktop image and puts new file Stacks at your fingertips for a stunning, clutter-free workspace.\nFinder: Browse your files like you browse your music with Cover Flow.\nTime Machine: See how your system looked on any given day and restore files with a\nWebsite: http://www.apple.com/macosx/leopard/features/'52EXEC dbo.InsertPlatform'Red Hat Linux','Some description for Red Hat Linux over here …'5354SET @.Log = @.Log +'Rows In Platform: ' + (SELECTCOUNT(PlatformID)FROM Platform) +'\n'55/* *** Start Insert PC *** */56SET @.Log = @.Log +'Start Insert PC' +'\n'5758DECLARE @.WinXPint, @.WinVistaint, @.OSXTigerint, @.OSXLeopardint, @.RedHatint59SET @.WinXP = (SELECT PlatformIDFROM PlatformWHERE Title ='Windows XP SP2 Professional Edition')60SET @.WinVista = (SELECT PlatformIDFROM PlatformWHERE Title ='Windows Vista Ultimate')61SET @.OSXTiger = (SELECT PlatformIDFROM PlatformWHERE Title ='Apple Mac OS X Tiger')62SET @.OSXLeopard = (SELECT PlatformIDFROM PlatformWHERE Title ='Apple Mac OS X Leopard')63SET @.RedHat = (SELECT PlatformIDFROM PlatformWHERE Title ='Red Hat Linux')6465EXEC dbo.InsertPC'Fusion PC One','Description here ...','Intel Core2 Duo E6600 2.4 GHz 1066MHz','1GB Dual Channel DDR2 667 SDRAM','120GB SATA2 NCQ HDD','NVIDIA GeForce 8600 256MB GDDR3','22" 3000:1 Wide Screen LCD', @.WinXP66EXEC dbo.InsertPC'Fusion PC Two','Description here ...','Intel Core2 Duo E6850 3 GHz 1333MHz','2GB Dual Channel DDR2 800 SDRAM','240GB SATA2 NCQ HDD','NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI','24" 3000:1 Wide Screen LCD', @.WinVista67EXEC dbo.InsertPC'Fusion PC Three','Description here ...','AMD Athlon 64 X2 Dual Core 6000+ 3 GHz','2GB Dual Channel DDR2 667 SDRAM','240GB SATA2 NCQ HDD','ATI Radeon Cross Fire 2900 256MB GDDR3','24" 3000:1 Wide Screen LCD', @.WinVista68EXEC dbo.InsertPC'Fusion X1','Description here ...','Intel Core2 Extreme Q6850 3 GHz 1333MHz','6GB Dual Channel DDR2 800 SDRAM','500GB SATA2 NCQ HDD','NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI','30" 3000:1 Wide Screen LCD', @.OSXLeopard69EXEC dbo.InsertPC'Fusion X2','Description here ...','AMD Athlon 64 FX 74 3 GHz','6GB Dual Channel DDR2 800 SDRAM','500GB SATA2 NCQ HDD','NVIDIA GeForce 8900 Ultra SLI 256MB GDDR3','30" 3000:1 Wide Screen LCD', @.WinVista70EXEC dbo.InsertPC'Fusion Tiger 1','Description here ...','Intel Core2 Duo E6600 2.4 GHz 1066MHz','2GB Dual Channel DDR2 800 SDRAM','120GB SATA2 NCQ HDD','NVIDIA GeForce 8600 256MB GDDR3 SLI','22" 3000:1 Wide Screen LCD', @.OSXTiger71EXEC dbo.InsertPC'Fusion Linux 1','Description here ...','AMD Athlon 64 X2 6000+ 3 GHz','1GB Dual Channel DDR2 800 SDRAM','120GB SATA2 NCQ HDD','NVIDIA GeForce 8600 256MB GDDR3','22" 3000:1 Wide Screen LCD', @.RedHat7273SET @.Log = @.Log +'Rows In PC: ' + (SELECTCOUNT(PCID)FROM PC) +'\n'7475/* *** Start Insert Inventory *** */76SET @.Log = @.Log +'Start Insert Inventory' +'\n'7778DECLARE @.F1int, @.F2int, @.F3int, @.FX1int, @.FX2int, @.FT1int, @.FR1int79SET @.F1 = (SELECT PCIDFROM PCWHERE Title ='Fusion PC One')80SET @.F2 = (SELECT PCIDFROM PCWHERE Title ='Fusion PC Two')81SET @.F3 = (SELECT PCIDFROM PCWHERE Title ='Fusion PC Three')82SET @.FX1 = (SELECT PCIDFROM PCWHERE Title ='Fusion X1')83SET @.FX2 = (SELECT PCIDFROM PCWHERE Title ='Fusion X2')84SET @.FT1 = (SELECT PCIDFROM PCWHERE Title ='Fusion Tiger One')85SET @.FR1 = (SELECT PCIDFROM PCWHERE Title ='Fusion Linux One')8687EXEC dbo.InsertInventory 10, @.F1, 2.5,'iCluster Fusion One'88EXEC dbo.InsertInventory 10, @.F2, 2.5,'iCluster Fusion Two'89EXEC dbo.InsertInventory 10, @.F3, 2.5,'iCluster Fusion Three'90EXEC dbo.InsertInventory 6, @.FX1, 6,'iCluster Fusion X1'91EXEC dbo.InsertInventory 6, @.FX2, 6,'iCluster Fusion X2'92EXEC dbo.InsertInventory 10, @.FT1, 3,'iCluster Fusion Tiger One'93EXEC dbo.InsertInventory 30, @.FR1, 2,'iCluster Fusion Linux One'9495SET @.Log = @.Log +'Rows In Inventory: ' + (SELECTCOUNT(InventoryID)FROM InventoryPC) +'\n'9697RETURN @.Log98
When the Forigen Keys are exist the Truncate statement will not be executed on the master table, even it is disabled.
You have 2 options,
Option 1: Use Delete From <table name> & disabled constraint
Option 2: Drop the FK on the child table, use the truncate statement then recreate the FK on your child table.
Friday, February 24, 2012
Cannot shrink log file
I know this is an age old question. I've looked through the posts and don't
find an answer.
I did a log backup this morning with the truncate option. Now I run "dbcc
shrinkfile" and get this:
Cannot shrink log file 2 (BSMOrders_Log) because all logical log files are
in use.
What causes this? I thought the backup would delete this data so the file
could be shrunk. It is still sitting at 12GB....Show all logical log files using DBCC Loginfo(<database id> ). If status is 2
for last log file then you will not be able to shrink it. Status 2 means it'
s
an active log and currently being used. In that case you can run some dummy
transactions to bring it to 0. Also you can look for any open transactions
that are not committed yet by using DBCC OPENTRAN
"Tim Greenwood" wrote:
> I know this is an age old question. I've looked through the posts and don
't
> find an answer.
> I did a log backup this morning with the truncate option. Now I run "dbcc
> shrinkfile" and get this:
> Cannot shrink log file 2 (BSMOrders_Log) because all logical log files are
> in use.
> What causes this? I thought the backup would delete this data so the file
> could be shrunk. It is still sitting at 12GB....
>
>|||Hey Bob
When you said "you can run some dummy transactions to bring it to 0",
could you please describe what does that really mean and how can
somebody run a transaction (can you please provide some sample SQL).
Sorry for lack of my knowledge on that
Thanks
PP
Bob wrote:[vbcol=seagreen]
> Show all logical log files using DBCC Loginfo(<database id> ). If status is
2
> for last log file then you will not be able to shrink it. Status 2 means i
t's
> an active log and currently being used. In that case you can run some dumm
y
> transactions to bring it to 0. Also you can look for any open transactions
> that are not committed yet by using DBCC OPENTRAN
> "Tim Greenwood" wrote:
>|||Lookup 'shrinking the transaction log' in Books Online for a detailed
explanation.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
<prabhupr@.hotmail.com> wrote in message
news:1152712375.701470.10780@.i42g2000cwa.googlegroups.com...
> Hey Bob
> When you said "you can run some dummy transactions to bring it to 0",
> could you please describe what does that really mean and how can
> somebody run a transaction (can you please provide some sample SQL).
> Sorry for lack of my knowledge on that
> Thanks
> PP
> Bob wrote:
>
find an answer.
I did a log backup this morning with the truncate option. Now I run "dbcc
shrinkfile" and get this:
Cannot shrink log file 2 (BSMOrders_Log) because all logical log files are
in use.
What causes this? I thought the backup would delete this data so the file
could be shrunk. It is still sitting at 12GB....Show all logical log files using DBCC Loginfo(<database id> ). If status is 2
for last log file then you will not be able to shrink it. Status 2 means it'
s
an active log and currently being used. In that case you can run some dummy
transactions to bring it to 0. Also you can look for any open transactions
that are not committed yet by using DBCC OPENTRAN
"Tim Greenwood" wrote:
> I know this is an age old question. I've looked through the posts and don
't
> find an answer.
> I did a log backup this morning with the truncate option. Now I run "dbcc
> shrinkfile" and get this:
> Cannot shrink log file 2 (BSMOrders_Log) because all logical log files are
> in use.
> What causes this? I thought the backup would delete this data so the file
> could be shrunk. It is still sitting at 12GB....
>
>|||Hey Bob
When you said "you can run some dummy transactions to bring it to 0",
could you please describe what does that really mean and how can
somebody run a transaction (can you please provide some sample SQL).
Sorry for lack of my knowledge on that
Thanks
PP
Bob wrote:[vbcol=seagreen]
> Show all logical log files using DBCC Loginfo(<database id> ). If status is
2
> for last log file then you will not be able to shrink it. Status 2 means i
t's
> an active log and currently being used. In that case you can run some dumm
y
> transactions to bring it to 0. Also you can look for any open transactions
> that are not committed yet by using DBCC OPENTRAN
> "Tim Greenwood" wrote:
>|||Lookup 'shrinking the transaction log' in Books Online for a detailed
explanation.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
<prabhupr@.hotmail.com> wrote in message
news:1152712375.701470.10780@.i42g2000cwa.googlegroups.com...
> Hey Bob
> When you said "you can run some dummy transactions to bring it to 0",
> could you please describe what does that really mean and how can
> somebody run a transaction (can you please provide some sample SQL).
> Sorry for lack of my knowledge on that
> Thanks
> PP
> Bob wrote:
>
Cannot shrink database log
I backup database and then truncate log with no_log option, then try to shrink the log file. It doesnot get shrunk
Also, Checked the sysprocesses. there are writelog under lastwaittype
Please help
Thanks
SanWhat about BACKUP LOG WITH NO_LOG? This should whack the t-log w/o having
to shrink. If this doesn't work, can you stop and restart the SQL service
and try the same thing again?
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||San,
For SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?ID=256650 and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
For SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/?ID=272318
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||Hi Dinesh
I have tried
1. backup database db to disk = 'c:\db.bak
2. backup log db with truncate_onl
3. dbcc shrinkfile(db_log,10
4. dbcc shrinkdatabase(db,20
Nothing works...!|||Thanks Dinesh...This indeed worked
The script presented to wrap aroun the tx log help solved it..
Microsoft Knowledge Base Article - 256650
INF: How to Shrink the SQL Server 7.0 Transaction Lo
Thanks once again for all your valuable help
San|||Thanks a lot Jim... I will try in future when it happens again..for now I got my solution.
Thank you Jim.
Also, Checked the sysprocesses. there are writelog under lastwaittype
Please help
Thanks
SanWhat about BACKUP LOG WITH NO_LOG? This should whack the t-log w/o having
to shrink. If this doesn't work, can you stop and restart the SQL service
and try the same thing again?
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||San,
For SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?ID=256650 and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
For SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/?ID=272318
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||Hi Dinesh
I have tried
1. backup database db to disk = 'c:\db.bak
2. backup log db with truncate_onl
3. dbcc shrinkfile(db_log,10
4. dbcc shrinkdatabase(db,20
Nothing works...!|||Thanks Dinesh...This indeed worked
The script presented to wrap aroun the tx log help solved it..
Microsoft Knowledge Base Article - 256650
INF: How to Shrink the SQL Server 7.0 Transaction Lo
Thanks once again for all your valuable help
San|||Thanks a lot Jim... I will try in future when it happens again..for now I got my solution.
Thank you Jim.
Cannot shrink database log
I backup database and then truncate log with no_log option, then try to shrink the log file. It doesnot get shrunk.
Also, Checked the sysprocesses. there are writelog under lastwaittype.
Please help.
Thanks,
San
What about BACKUP LOG WITH NO_LOG? This should whack the t-log w/o having
to shrink. If this doesn't work, can you stop and restart the SQL service
and try the same thing again?
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San
|||San,
For SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?ID=256650 and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
For SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/?ID=272318
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San
|||Hi Dinesh,
I have tried:
1. backup database db to disk = 'c:\db.bak'
2. backup log db with truncate_only
3. dbcc shrinkfile(db_log,10)
4. dbcc shrinkdatabase(db,20)
Nothing works...!
|||Thanks Dinesh...This indeed worked
The script presented to wrap aroun the tx log help solved it..!
Microsoft Knowledge Base Article - 256650
INF: How to Shrink the SQL Server 7.0 Transaction Log
Thanks once again for all your valuable help.
San
|||Thanks a lot Jim... I will try in future when it happens again..for now I got my solution..
Thank you Jim.
Also, Checked the sysprocesses. there are writelog under lastwaittype.
Please help.
Thanks,
San
What about BACKUP LOG WITH NO_LOG? This should whack the t-log w/o having
to shrink. If this doesn't work, can you stop and restart the SQL service
and try the same thing again?
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San
|||San,
For SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?ID=256650 and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
For SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/?ID=272318
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San
|||Hi Dinesh,
I have tried:
1. backup database db to disk = 'c:\db.bak'
2. backup log db with truncate_only
3. dbcc shrinkfile(db_log,10)
4. dbcc shrinkdatabase(db,20)
Nothing works...!
|||Thanks Dinesh...This indeed worked
The script presented to wrap aroun the tx log help solved it..!
Microsoft Knowledge Base Article - 256650
INF: How to Shrink the SQL Server 7.0 Transaction Log
Thanks once again for all your valuable help.
San
|||Thanks a lot Jim... I will try in future when it happens again..for now I got my solution..
Thank you Jim.
Cannot shrink database log
I backup database and then truncate log with no_log option, then try to shr
ink the log file. It doesnot get shrunk.
Also, Checked the sysprocesses. there are writelog under lastwaittype.
Please help.
Thanks,
SanWhat about BACKUP LOG WITH NO_LOG? This should whack the t-log w/o having
to shrink. If this doesn't work, can you stop and restart the SQL service
and try the same thing again?
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||San,
For SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?ID=256650 and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
For SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/?ID=272318
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||Hi Dinesh,
I have tried:
1. backup database db to disk = 'c:\db.bak'
2. backup log db with truncate_only
3. dbcc shrinkfile(db_log,10)
4. dbcc shrinkdatabase(db,20)
Nothing works...!|||Thanks Dinesh...This indeed worked
The script presented to wrap aroun the tx log help solved it..!
Microsoft Knowledge Base Article - 256650
INF: How to Shrink the SQL Server 7.0 Transaction Log
Thanks once again for all your valuable help.
San|||Thanks a lot Jim... I will try in future when it happens again..for now I go
t my solution..
Thank you Jim.
ink the log file. It doesnot get shrunk.
Also, Checked the sysprocesses. there are writelog under lastwaittype.
Please help.
Thanks,
SanWhat about BACKUP LOG WITH NO_LOG? This should whack the t-log w/o having
to shrink. If this doesn't work, can you stop and restart the SQL service
and try the same thing again?
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||San,
For SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?ID=256650 and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
For SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/?ID=272318
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||Hi Dinesh,
I have tried:
1. backup database db to disk = 'c:\db.bak'
2. backup log db with truncate_only
3. dbcc shrinkfile(db_log,10)
4. dbcc shrinkdatabase(db,20)
Nothing works...!|||Thanks Dinesh...This indeed worked
The script presented to wrap aroun the tx log help solved it..!
Microsoft Knowledge Base Article - 256650
INF: How to Shrink the SQL Server 7.0 Transaction Log
Thanks once again for all your valuable help.
San|||Thanks a lot Jim... I will try in future when it happens again..for now I go
t my solution..
Thank you Jim.
Subscribe to:
Posts (Atom)