|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL7 truncate problemI'm not sure if this is the place to post this, but I didn't see a general admin group... I have to touch SQL about three times a year. This is one of the times... grin I needed to delete some data from a table in SQL7. I managed to do that, but it grew the database and the log to do so. OK. That's expected. I now need to release the free space back to the OS. Currently, I have 5.3GB used and 10.2GB free in the database and 26MB used and 12GB free in the log. I do not have enough space on the drive to run a backup of any sort. (Old server, old drives, I know, I know. You're preaching to the choir.) I have not stopped the SQL server. I do not know the consequences of doing that. grin I have tried: In Enterprise Manager: Right clicking on the database and clicking on Shrink Database Right clicking on the database and clicking on Truncate Log In Query Analyzer: dbcc shrinkdatabase (database, truncateonly) dbcc shrinkfile (data, truncateonly) dbcc shrinkfile (log, truncateonly) dbcc shrinkfile (log 6400) (its minimum size) Looking at the return of the results after the shrinkfile, it still thinks that all the space is in use: DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages ------ ------ ----------- ----------- ----------- -------------- 7 2 1652840 6400 1652840 6400 Any ideas as to how to get back this space? I'll admit, I'm a SQL neophyte. I hate having to touch it because each time I run this same procedure, it never behaves the same way twice.... grin Thanks in advance, Cappy try:
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE sp_ShrinkLogFile AS ----- DBCC SHRINKFILE ("<log name>" , TRUNCATEONLY ) BACKUP LOG "<database name>" WITH TRUNCATE_ONLY ------ CREATE TABLE MyTable ( MyField VARCHAR(10) , PK INT ) INSERT Mytable (PK) VALUES (1) SET NOCOUNT ON DECLARE @Index INT SELECT @Index = 0 WHILE (@Index < 20000) BEGIN UPDATE MyTable SET MyField = MyField WHERE PK = 1 /* Some criteria to restrict to one row. */ SELECT @Index = @Index + 1 END SET NOCOUNT OFF --------- DBCC SHRINKFILE ( "<log name>" , TRUNCATEONLY ) BACKUP LOG "<database name>" WITH TRUNCATE_ONLY --------- drop table Mytable GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO "Cappy" schreef: Show quote > Hello! > > I'm not sure if this is the place to post this, but I didn't see a general admin > group... > > I have to touch SQL about three times a year. This is one of the times... grin > > I needed to delete some data from a table in SQL7. I managed to do that, but it > grew the database and the log to do so. OK. That's expected. I now need to > release the free space back to the OS. > > Currently, I have 5.3GB used and 10.2GB free in the database and 26MB used and > 12GB free in the log. > > I do not have enough space on the drive to run a backup of any sort. (Old > server, old drives, I know, I know. You're preaching to the choir.) > I have not stopped the SQL server. I do not know the consequences of doing > that. grin > > I have tried: > In Enterprise Manager: > Right clicking on the database and clicking on Shrink Database > Right clicking on the database and clicking on Truncate Log > > In Query Analyzer: > dbcc shrinkdatabase (database, truncateonly) > dbcc shrinkfile (data, truncateonly) > dbcc shrinkfile (log, truncateonly) > dbcc shrinkfile (log 6400) (its minimum size) > > Looking at the return of the results after the shrinkfile, it still thinks that > all the space is in use: > > DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages > ------ ------ ----------- ----------- ----------- -------------- > 7 2 1652840 6400 1652840 6400 > > Any ideas as to how to get back this space? I'll admit, I'm a SQL neophyte. I > hate having to touch it because each time I run this same procedure, it never > behaves the same way twice.... grin > > Thanks in advance, > Cappy > > > Um, OK. I think you missed where I said I was a SQL neophyte. grin Why do you
create the table and run the shrinkfile and backup twice? Thanks loads, Cappy Show quote "ron" <r**@discussions.microsoft.com> wrote in message news:7D066394-F98A-43A6-8426-D08F0E4B68C3@microsoft.com... > try: > > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_NULLS OFF > GO > > CREATE PROCEDURE sp_ShrinkLogFile AS > > > > ----- > DBCC SHRINKFILE ("<log name>" , TRUNCATEONLY ) > > BACKUP LOG "<database name>" WITH TRUNCATE_ONLY > > > > ------ > CREATE TABLE MyTable ( > > MyField VARCHAR(10) > > , PK INT ) > > INSERT Mytable (PK) VALUES (1) > > > SET NOCOUNT ON > > DECLARE @Index INT > > SELECT @Index = 0 > > WHILE (@Index < 20000) > > BEGIN > > UPDATE MyTable > SET MyField = MyField > WHERE PK = 1 /* Some criteria to restrict to one row. */ > SELECT @Index = @Index + 1 > END > > > SET NOCOUNT OFF > > --------- > > DBCC SHRINKFILE ( "<log name>" , TRUNCATEONLY ) > > BACKUP LOG "<database name>" WITH TRUNCATE_ONLY > --------- > > drop table Mytable > GO > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS ON > GO > > > > > > "Cappy" schreef: > > > Hello! > > > > I'm not sure if this is the place to post this, but I didn't see a general admin > > group... > > > > I have to touch SQL about three times a year. This is one of the times... grin > > > > I needed to delete some data from a table in SQL7. I managed to do that, but it > > grew the database and the log to do so. OK. That's expected. I now need to > > release the free space back to the OS. > > > > Currently, I have 5.3GB used and 10.2GB free in the database and 26MB used and > > 12GB free in the log. > > > > I do not have enough space on the drive to run a backup of any sort. (Old > > server, old drives, I know, I know. You're preaching to the choir.) > > I have not stopped the SQL server. I do not know the consequences of doing > > that. grin > > > > I have tried: > > In Enterprise Manager: > > Right clicking on the database and clicking on Shrink Database > > Right clicking on the database and clicking on Truncate Log > > > > In Query Analyzer: > > dbcc shrinkdatabase (database, truncateonly) > > dbcc shrinkfile (data, truncateonly) > > dbcc shrinkfile (log, truncateonly) > > dbcc shrinkfile (log 6400) (its minimum size) > > > > Looking at the return of the results after the shrinkfile, it still thinks that > > all the space is in use: > > > > DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages > > ------ ------ ----------- ----------- ----------- -------------- > > 7 2 1652840 6400 1652840 6400 > > > > Any ideas as to how to get back this space? I'll admit, I'm a SQL neophyte. I > > hate having to touch it because each time I run this same procedure, it never > > behaves the same way twice.... grin > > > > Thanks in advance, > > Cappy > > > > > > try
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE sp_ShrinkLogFile AS ----- DBCC SHRINKFILE ("<log name>" , TRUNCATEONLY ) BACKUP LOG "<database name>" WITH TRUNCATE_ONLY ------ CREATE TABLE MyTable ( MyField VARCHAR(10) , PK INT ) INSERT Mytable (PK) VALUES (1) SET NOCOUNT ON DECLARE @Index INT SELECT @Index = 0 WHILE (@Index < 20000) BEGIN UPDATE MyTable SET MyField = MyField WHERE PK = 1 /* Some criteria to restrict to one row. */ SELECT @Index = @Index + 1 END SET NOCOUNT OFF --------- DBCC SHRINKFILE ( "<log name>" , TRUNCATEONLY ) BACKUP LOG "<database name>" WITH TRUNCATE_ONLY --------- drop table Mytable GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO "Cappy" schreef: Show quote > Hello! > > I'm not sure if this is the place to post this, but I didn't see a general admin > group... > > I have to touch SQL about three times a year. This is one of the times... grin > > I needed to delete some data from a table in SQL7. I managed to do that, but it > grew the database and the log to do so. OK. That's expected. I now need to > release the free space back to the OS. > > Currently, I have 5.3GB used and 10.2GB free in the database and 26MB used and > 12GB free in the log. > > I do not have enough space on the drive to run a backup of any sort. (Old > server, old drives, I know, I know. You're preaching to the choir.) > I have not stopped the SQL server. I do not know the consequences of doing > that. grin > > I have tried: > In Enterprise Manager: > Right clicking on the database and clicking on Shrink Database > Right clicking on the database and clicking on Truncate Log > > In Query Analyzer: > dbcc shrinkdatabase (database, truncateonly) > dbcc shrinkfile (data, truncateonly) > dbcc shrinkfile (log, truncateonly) > dbcc shrinkfile (log 6400) (its minimum size) > > Looking at the return of the results after the shrinkfile, it still thinks that > all the space is in use: > > DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages > ------ ------ ----------- ----------- ----------- -------------- > 7 2 1652840 6400 1652840 6400 > > Any ideas as to how to get back this space? I'll admit, I'm a SQL neophyte. I > hate having to touch it because each time I run this same procedure, it never > behaves the same way twice.... grin > > Thanks in advance, > Cappy > > > Whew! Went to that webpage and tried to make sense of it. Way over my head!
grin But thanks... I'll plow through it again... Cappy Show quote "Scott Morris" <bo***@bogus.com> wrote in message news:eg7L7l8gFHA.328@tk2msftngp13.phx.gbl... > http://www.lazydba.com/sql/1__3722.html > > I'll over simplify. You will not be able to shrink the log file passed the
last used segment. The command "DBCC LOGINFO (mydatabse)" will show you the segments for the log for mydatabase (replace this with your actual database name - note the absence of quote marks). In the result set of the command, note the non-zero values in the status column. The last row with a non-zero value is the active extent. To really shrink the file, the active extent should be at the top of this list. If this is not so, then you must move the active segment. To do this, you only need to perform a sufficient number of dml statements. Below is a much better explanation, along with better links. http://www.karaszi.com/SQLServer/info_dont_shrink.asp Thanks. After running the loginfo, I figured the one w/ the "2" in the status
was the active one. I have managed to recover 10GB out of the log and run a SQL backup of the database, the master, and the msdb. I feel so much better that I have done that. I now need to recover the free space out of the data file... grin Thanks again... I'm bookmarking this one... Cappy Show quote "Scott Morris" <bo***@bogus.com> wrote in message news:O$VjJY9gFHA.3300@TK2MSFTNGP15.phx.gbl... > I'll over simplify. You will not be able to shrink the log file passed the > last used segment. The command "DBCC LOGINFO (mydatabse)" will show you the > segments for the log for mydatabase (replace this with your actual database > name - note the absence of quote marks). In the result set of the command, > note the non-zero values in the status column. The last row with a non-zero > value is the active extent. To really shrink the file, the active extent > should be at the top of this list. If this is not so, then you must move > the active segment. To do this, you only need to perform a sufficient > number of dml statements. > > Below is a much better explanation, along with better links. > http://www.karaszi.com/SQLServer/info_dont_shrink.asp > >  |
|||||||||||||||||||||||
Other interesting topics