Help Backup: transaction

Showing posts with label transaction. Show all posts

Wednesday, March 21, 2012

Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files in SQL Server?
ThanksBACKUP LOG file and then if you want to reduce physical size of the file run
DBCC SHRINKFILE command
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8C79F464-72E9-4138-BF51-0824857DEEA4@.microsoft.com...
> Hi guys, how to monitor and purge log files/transaction files in SQL
> Server?
> Thanks
>

Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files in SQL Server?
Thanks
BACKUP LOG file and then if you want to reduce physical size of the file run
DBCC SHRINKFILE command
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8C79F464-72E9-4138-BF51-0824857DEEA4@.microsoft.com...
> Hi guys, how to monitor and purge log files/transaction files in SQL
> Server?
> Thanks
>

Friday, March 9, 2012

HELP--still cannot shrink the transaction log--PLEASE HELP

Hello:
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sNevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:
> Hello:
> This is an update to my other posting. I know that it is unorthodox to post
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only has
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>|||> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
> "childofthe1980s" wrote:
>> Hello:
>> This is an update to my other posting. I know that it is unorthodox to post
>> twice but I don't know what else to do.
>> Below is my original posting. I had already done the items posted by the
>> other gentleman such as reivewing the sysfiles. I cannot get the logical
>> file name from anywhere either.
>> Again, please help! Should I just not shrink the file. The client only has
>> 1.82 GB of free space left and there is nothing else that can be deleted.
>> Below is my message:
>> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> analyzer in SQL 2000 against the master database.
>> What is the EXACT syntax? You see, the name of the database is JC and the
>> log file is named GPSJClog.ldf. I have tried to place both names in the
>> syntax of the statement but with no success. I keep getting a message saying
>> that the file name is not in sysfiles.
>> This is what I have tried:
>> DBCC SHRINKFILE (GPJClog.ldf , 2)
>> Please help!
>> childofthe1980s|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
>> You see, the support documentation on the web did not say that you
>> have to
>> take the fileID of the database--2 in the syntax above-- in order to
>> make it
>> work! You get the number 2 from running the following query against the
>> database:
>> select * from sysfiles
>> It would have been nice if this had been in the documentation!
> The documentation clearly states that you can use *either* the logical
> filename or the file id. The documentation is correct. The command you
> posted had 'GPSJCLog.ldf' for the logical filename, and I guess that you
> by mistake put the *physical* filename there instead of the *logical*
> filename. If you show ut the output from below, we can say for sure:
> SELECT name, filename FROM sysfiles
> I have shrunk database files occasionally and I always used the logical
> filename. In addition, here's a script (executed on my SQL Server 2000)
> which clearly show the usage of the *logical* filename:
> USE master
> CREATE DATABASE myTestShrink ON PRIMARY
> (NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
> ,SIZE = 1024KB , FILEGROWTH = 10%)
> LOG ON
> (NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
> , SIZE = 1024KB , FILEGROWTH = 10%)
> GO
> --Increase log file size
> BACKUP DATABASE myTestShrink TO DISK = 'nul'
> CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
> INSERT INTO myTestShrink..test(c2)
> SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
> GO
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> --Empty the log
> BACKUP LOG myTestShrink WITH NO_LOG
> --Check the logical and physical files names
> SELECT name, filename FROM myTestShrink..sysfiles
> --Perform the shrink, ***based on logical filename***
> USE myTestShrink
> GO
> DBCC SHRINKFILE('myTestShrink_log', 2)
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> USE master
>
...just to add to Tibor's response. You can retreive the logical
filename by running sp_helpdb 'YourDatabaseName'.
--
Regards
Steen SchlÃ¼ter Persson
Database Administrator / System Administrator|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:
> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
>|||childofthe1980s wrote:
> No, I did all different combinations--with ldf, without ldf, against the
> master, against the database, and nothing was working until I placed the
> database fileID of "2" in the suntax.
> So, yes the documentation needs updating!
>
I think you need to show up the documentation that's wrong. If you look
in BOL, the syntax is :
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
where file_name and file_id is explained as
"file_name
Is the logical name of the file shrunk. File names must conform to the
rules for identifiers. For more information, see Using Identifiers. "
"file_id
Is the identification (ID) number of the file to be shrunk. To obtain a
file ID, use the FILE_ID function or search sysfiles in the current
database."
I only see that it says you can use the LOGICAL filename or the fileID.
I don't see it mentions the physical filename anywhere?
--
Regards
Steen SchlÃ¼ter Persson
Database Administrator / System Administrator

HELP--still cannot shrink the transaction log--PLEASE HELP

Hello:
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message sayin
g
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sNevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:

> Hello:
> This is an update to my other posting. I know that it is unorthodox to po
st
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only h
as
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message say
ing
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>|||> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
The documentation clearly states that you can use *either* the logical filen
ame or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the
logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *
logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical file
name. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usag
e of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
USE master
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...[vbcol=seagreen]
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
> "childofthe1980s" wrote:
>|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:

> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
> The documentation clearly states that you can use *either* the logical
> filename or the file id. The documentation is correct. The command you
> posted had 'GPSJCLog.ldf' for the logical filename, and I guess that you
> by mistake put the *physical* filename there instead of the *logical*
> filename. If you show ut the output from below, we can say for sure:
> SELECT name, filename FROM sysfiles
> I have shrunk database files occasionally and I always used the logical
> filename. In addition, here's a script (executed on my SQL Server 2000)
> which clearly show the usage of the *logical* filename:
> USE master
> CREATE DATABASE myTestShrink ON PRIMARY
> (NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
> ,SIZE = 1024KB , FILEGROWTH = 10%)
> LOG ON
> (NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
> , SIZE = 1024KB , FILEGROWTH = 10%)
> GO
> --Increase log file size
> BACKUP DATABASE myTestShrink TO DISK = 'nul'
> CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
> INSERT INTO myTestShrink..test(c2)
> SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
> GO
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> --Empty the log
> BACKUP LOG myTestShrink WITH NO_LOG
> --Check the logical and physical files names
> SELECT name, filename FROM myTestShrink..sysfiles
> --Perform the shrink, ***based on logical filename***
> USE myTestShrink
> GO
> DBCC SHRINKFILE('myTestShrink_log', 2)
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> USE master
>
...just to add to Tibor's response. You can retreive the logical
filename by running sp_helpdb 'YourDatabaseName'.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:

> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
>
>|||childofthe1980s wrote:
> No, I did all different combinations--with ldf, without ldf, against the
> master, against the database, and nothing was working until I placed the
> database fileID of "2" in the suntax.
> So, yes the documentation needs updating!
>
I think you need to show up the documentation that's wrong. If you look
in BOL, the syntax is :
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
where file_name and file_id is explained as
"file_name
Is the logical name of the file shrunk. File names must conform to the
rules for identifiers. For more information, see Using Identifiers. "
"file_id
Is the identification (ID) number of the file to be shrunk. To obtain a
file ID, use the FILE_ID function or search sysfiles in the current
database."
I only see that it says you can use the LOGICAL filename or the fileID.
I don't see it mentions the physical filename anywhere?
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

HELP--still cannot shrink the transaction log--PLEASE HELP

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sYou specify the *logical name of the file*. See the sysfiles table. Also, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> Hello:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s|||I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:
> You specify the *logical name of the file*. See the sysfiles table. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> > Hello:
> >
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
> > What is the EXACT syntax? You see, the name of the database is JC and the
> > log file is named GPSJClog.ldf. I have tried to place both names in the
> > syntax of the statement but with no success. I keep getting a message saying
> > that the file name is not in sysfiles.
> >
> > This is what I have tried:
> >
> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> > Please help!
> >
> > childofthe1980s
>|||Can you post the commands you tried to execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>I have done both of those things--no success...
> PLEASE HELP!!!
> childofthe1980s
> "Tibor Karaszi" wrote:
>> You specify the *logical name of the file*. See the sysfiles table. Also, see
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
>> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> > Hello:
>> >
>> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> > analyzer in SQL 2000 against the master database.
>> >
>> > What is the EXACT syntax? You see, the name of the database is JC and the
>> > log file is named GPSJClog.ldf. I have tried to place both names in the
>> > syntax of the statement but with no success. I keep getting a message saying
>> > that the file name is not in sysfiles.
>> >
>> > This is what I have tried:
>> >
>> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> > Please help!
>> >
>> > childofthe1980s
>>|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:
> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
> >I have done both of those things--no success...
> >
> > PLEASE HELP!!!
> >
> > childofthe1980s
> >
> > "Tibor Karaszi" wrote:
> >
> >> You specify the *logical name of the file*. See the sysfiles table. Also, see
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> >> > Hello:
> >> >
> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >> > analyzer in SQL 2000 against the master database.
> >> >
> >> > What is the EXACT syntax? You see, the name of the database is JC and the
> >> > log file is named GPSJClog.ldf. I have tried to place both names in the
> >> > syntax of the statement but with no success. I keep getting a message saying
> >> > that the file name is not in sysfiles.
> >> >
> >> > This is what I have tried:
> >> >
> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> > Please help!
> >> >
> >> > childofthe1980s
> >>
> >>
>|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:
>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message saying
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:
> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
> >Hello:
> >
> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >analyzer in SQL 2000 against the master database.
> >
> >What is the EXACT syntax? You see, the name of the database is JC and the
> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >syntax of the statement but with no success. I keep getting a message saying
> >that the file name is not in sysfiles.
> >
> >This is what I have tried:
> >
> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> >Please help!
> >
> >childofthe1980s
>|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
>> Can you post the commands you tried to execute?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message
>> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>> >I have done both of those things--no success...
>> >
>> > PLEASE HELP!!!
>> >
>> > childofthe1980s
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> You specify the *logical name of the file*. See the sysfiles table.
>> >> Also, see
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> >> > Hello:
>> >> >
>> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE
>> >> > in query
>> >> > analyzer in SQL 2000 against the master database.
>> >> >
>> >> > What is the EXACT syntax? You see, the name of the database is JC
>> >> > and the
>> >> > log file is named GPSJClog.ldf. I have tried to place both names in
>> >> > the
>> >> > syntax of the statement but with no success. I keep getting a
>> >> > message saying
>> >> > that the file name is not in sysfiles.
>> >> >
>> >> > This is what I have tried:
>> >> >
>> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> > Please help!
>> >> >
>> >> > childofthe1980s
>> >>
>> >>
>>|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
>> Where you IN the database in question? I believe that is required.
>> Roy Harvey
>> Beacon Falls, CT
>> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >Hello:
>> >
>> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >query
>> >analyzer in SQL 2000 against the master database.
>> >
>> >What is the EXACT syntax? You see, the name of the database is JC and
>> >the
>> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >syntax of the statement but with no success. I keep getting a message
>> >saying
>> >that the file name is not in sysfiles.
>> >
>> >This is what I have tried:
>> >
>> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> >Please help!
>> >
>> >childofthe1980s|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:
> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost the
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> > Nevermind. I figured it out.
> >
> > I had to run the following scripts against each of the databases:
> >
> > BACKUP LOG JC with TRUNCATE_ONLY
> > DBCC SHRINKFILE (2, 20)
> >
> > You see, the support documentation on the web did not say that you have to
> > take the fileID of the database--2 in the syntax above-- in order to make
> > it
> > work! You get the number 2 from running the following query against the
> > database:
> >
> > select * from sysfiles
> >
> > It would have been nice if this had been in the documentation!
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
> > "Roy Harvey" wrote:
> >
> >> Where you IN the database in question? I believe that is required.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> >> <childofthe1980s@.discussions.microsoft.com> wrote:
> >>
> >> >Hello:
> >> >
> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
> >> >query
> >> >analyzer in SQL 2000 against the master database.
> >> >
> >> >What is the EXACT syntax? You see, the name of the database is JC and
> >> >the
> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >> >syntax of the statement but with no success. I keep getting a message
> >> >saying
> >> >that the file name is not in sysfiles.
> >> >
> >> >This is what I have tried:
> >> >
> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> >Please help!
> >> >
> >> >childofthe1980s
> >>
>
>|||> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:04A6393F-154E-4A1A-946B-0768F7B47F85@.microsoft.com...
> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
> "Andrew J. Kelly" wrote:
>> Not true. You can use either the ID or the LOGICAL name not the physical
>> name. And hopefully the db is not in FULL recovery mode or you just lost the
>> log chain with that truncate.
>> --
>> Andrew J. Kelly SQL MVP
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>> > Nevermind. I figured it out.
>> >
>> > I had to run the following scripts against each of the databases:
>> >
>> > BACKUP LOG JC with TRUNCATE_ONLY
>> > DBCC SHRINKFILE (2, 20)
>> >
>> > You see, the support documentation on the web did not say that you have to
>> > take the fileID of the database--2 in the syntax above-- in order to make
>> > it
>> > work! You get the number 2 from running the following query against the
>> > database:
>> >
>> > select * from sysfiles
>> >
>> > It would have been nice if this had been in the documentation!
>> >
>> > Thanks!
>> >
>> > childofthe1980s
>> >
>> >
>> >
>> >
>> >
>> > "Roy Harvey" wrote:
>> >
>> >> Where you IN the database in question? I believe that is required.
>> >>
>> >> Roy Harvey
>> >> Beacon Falls, CT
>> >>
>> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> >> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hello:
>> >> >
>> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >> >query
>> >> >analyzer in SQL 2000 against the master database.
>> >> >
>> >> >What is the EXACT syntax? You see, the name of the database is JC and
>> >> >the
>> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >> >syntax of the statement but with no success. I keep getting a message
>> >> >saying
>> >> >that the file name is not in sysfiles.
>> >> >
>> >> >This is what I have tried:
>> >> >
>> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> >Please help!
>> >> >
>> >> >childofthe1980s
>> >>
>>

HELP--cannot shrink the transaction log

Wednesday, March 7, 2012

HELP: Transaction Log Backup Files

Hello world,
I am having some diffulty finding the method to truncate my MSSQL
transaction logs files.
I am successful backing up the system and user tables and the logs ( using
a maintianace wizard, But there isnt an option for logfile truncate'
1) Do Maintance Wizards truncate the log files automatically?
2) WHERE can i find this option?
RussTruncating the transaction log from Query Analyzer:
BACKUP LOG database_name WITH NO_LOG or TRUNCATE_ONLY
This statement will never backup log actualy but truncate inactive portion of log file.
It's better idea to let the SQL Server do all that through maintenance plans and regular log backup bacause after every log backup SQL will truncate inactive portion of log file.
Also you should check db recovery options!
More info BOL - "Truncating the Transaction Log"
hope it helps|||Why would you want to truncate the log file regularly? It only cost performance etc to truncate it
and then having it autogrow back...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stephen Driscoll" <info@.fnc.co.uk> wrote in message news:%23bjBigKwDHA.2460@.TK2MSFTNGP10.phx.gbl...
> Hello world,
> I am having some diffulty finding the method to truncate my MSSQL
> transaction logs files.
> I am successful backing up the system and user tables and the logs ( using
> a maintianace wizard, But there isnt an option for logfile truncate'
> 1) Do Maintance Wizards truncate the log files automatically?
> 2) WHERE can i find this option?
> Russ
>

Monday, February 27, 2012

Help: query to get total last 7 day sales for each day

Let say I have this table in MS SQL server
table transaction(date,sales)

how to query to get result like this (date,sales,sum(sales last 7 day))
I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.

note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day

thanksselect date,sum(sales) from table where date between (getdate()-7) and getdate()
group by date|||That will get you sales within the last 7x24 hours, because getdate returns the full date and time. Maybe that's what you want, but if you are looking at full sales days you should try the datediff function:

select convert(char(10), date, 120), sum(sales)
from table
where datediff(yourdate, getdate()) < 7 --(or 6, depending upon whether you want to count the current day)
group by convert(char(10), date, 120)

Friday, February 24, 2012

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.

I've made backup of transaction log at 17:05

When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.

It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.

But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!

Any help is appreciated.

PagusRestore the full backup from 13:00 with NORECOVERY
Restore the tLog backup to 15:10 with Recovery

Should work.

Rick

"Pagus" <pagus@.writeme.com> wrote in message
news:5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Restore the full backup from 13:00 with NORECOVERY
> Restore the tLog backup to 15:10 with Recovery

I have not done this, but you may want to restore tlog backup to 15:10 with
a standby file.

I THINK you can then restore in time past 15:10 again w/o having to do the
full restore.

(hmm, I should try this now that I think about... too tired right now
though. :-)

> Should work.
>
> Rick|||> (hmm, I should try this now that I think about... too tired right now
> though. :-)

No need to Greg. I have a demo script just for this. You can restore the same tlog several times,
going forward in time, using STANDBY. I also checked with MS and this is a tested and supported
method. Here are my comments on the topic:
http://www.karaszi.com/SQLServer/in...veral_times.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mbndd.312510$bp1.26137@.twister.nyroc.rr.com.. .
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Restore the full backup from 13:00 with NORECOVERY
>> Restore the tLog backup to 15:10 with Recovery
> I have not done this, but you may want to restore tlog backup to 15:10 with
> a standby file.
> I THINK you can then restore in time past 15:10 again w/o having to do the
> full restore.
> (hmm, I should try this now that I think about... too tired right now
> though. :-)
>
>>
>> Should work.
>>
>>
>> Rick
>>
>>|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus

Always used transaction logs to do a point in time restore.

Vincento|||Hi there,

a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.

first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.

Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.
I've made backup of transaction log at 17:05
When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.
It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.
But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!
Any help is appreciated.
PagusRestore the full backup from 13:00 with NORECOVERY
Restore the tLog backup to 15:10 with Recovery
Should work.
Rick
"Pagus" <pagus@.writeme.com> wrote in message
news:5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||Hi Pagus,
Point in time recovery can be done with tlogs.... had you taken tlogs after
15:00 full backup?
Thanks
GYK
"Pagus" wrote:
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Restore the full backup from 13:00 with NORECOVERY
> Restore the tLog backup to 15:10 with Recovery
I have not done this, but you may want to restore tlog backup to 15:10 with
a standby file.
I THINK you can then restore in time past 15:10 again w/o having to do the
full restore.
(hmm, I should try this now that I think about... too tired right now
though. :-)
> Should work.
>
> Rick
>|||To summarize what u currently have :
[1] First Full Database Backup @. 13:00
[2] Second Full Database Backup @. 17:00
[3] TLog @. 17:05
Trying to Point-In-Time restore to 15:10
Considering that the Point-In-Time restore is before the second DB backup,
the Second DB backup is irrelevent.
There are a few things that u shud check
[1] Was the DB in valid state when you issued the Backup @. 13:00
[2] Check the duration (Period) for the TLog backup. It should have an
end time of 17:05 and start time of what ?
Thanks,
Gopi
"Pagus" wrote:
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||> (hmm, I should try this now that I think about... too tired right now
> though. :-)
No need to Greg. I have a demo script just for this. You can restore the same tlog several times,
going forward in time, using STANDBY. I also checked with MS and this is a tested and supported
method. Here are my comments on the topic:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mbndd.312510$bp1.26137@.twister.nyroc.rr.com...
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Restore the full backup from 13:00 with NORECOVERY
>> Restore the tLog backup to 15:10 with Recovery
> I have not done this, but you may want to restore tlog backup to 15:10 with
> a standby file.
> I THINK you can then restore in time past 15:10 again w/o having to do the
> full restore.
> (hmm, I should try this now that I think about... too tired right now
> though. :-)
>
>> Should work.
>>
>> Rick
>>
>|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
Always used transaction logs to do a point in time restore.
Vincento|||Hi there,
a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.
first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.
Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

Wednesday, March 21, 2012

Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files? Tha

Friday, March 9, 2012

HELP--still cannot shrink the transaction log--PLEASE HELP

HELP--still cannot shrink the transaction log--PLEASE HELP

HELP--still cannot shrink the transaction log--PLEASE HELP

HELP--cannot shrink the transaction log

HELP--cannot shrink the transaction log

HELP--cannot shrink the transaction log

Wednesday, March 7, 2012

HELP: Transaction Log Backup Files

Monday, February 27, 2012

Help: query to get total last 7 day sales for each day

Friday, February 24, 2012

HELP: Point in time restore : HOW ?

HELP: Point in time restore : HOW ?

HELP: Point in time restore : HOW ?

HELP: Point in time restore : HOW ?

Help Backup

Blog Archive

About Me