No execution plan in profiler

I'm trying to track down a problem with a very simple sql insert statement.
INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM
tblRecordingId
This statement has recently begun to cause timeouts (but not all the time).
When it does work, I get this plan:
Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
[tblRecordingId].[AccountId]=RaiseIfNull(82440)))
|--Top(1)
|--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
|--Stream
Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
|--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
CPU=16, Reads=121, Writes=0, Duration=10860.
When it causes a timeout, there's no execution plan in the profiler. CPU=15,
Reads=40, Writes=0, Duration=40000.
tblRecordingId contains 2 int columns and 590 rows. There are no indexes or
keys.
I did add deadlock to the profiler but didn't get any entries. The server is
not doing much other work (cpu usage is very low.)
I also tried it in the query analyzer. The query usually executes in less
than a second, but sometimes it takes more than a minute.
Any ideas on what the problem is or hints on what to search for?
PeterMost probably a locking problem. The subquery that gets the MAX value does a
table scan. Can you add
an index on the RecordingId column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter Laan" <plnews2000@.yahoo.se> wrote in message news:e7S7Uly0FHA.3780@.TK2MSFTNGP12.phx.
gbl...
> I'm trying to track down a problem with a very simple sql insert statement
.
> INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM tblRecord
ingId
> This statement has recently begun to cause timeouts (but not all the time)
. When it does work, I
> get this plan:
> Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
> SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
> [tblRecordingId].[AccountId]=RaiseIfNull(82440)))
> |--Top(1)
> |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
> |--Stream Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
> |--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
> CPU=16, Reads=121, Writes=0, Duration=10860.
> When it causes a timeout, there's no execution plan in the profiler. CPU=1
5, Reads=40, Writes=0,
> Duration=40000.
> tblRecordingId contains 2 int columns and 590 rows. There are no indexes o
r keys.
> I did add deadlock to the profiler but didn't get any entries. The server
is not doing much other
> work (cpu usage is very low.)
> I also tried it in the query analyzer. The query usually executes in less
than a second, but
> sometimes it takes more than a minute.
>
> Any ideas on what the problem is or hints on what to search for?
>
> Peter
>|||Peter Laan wrote:
> I'm trying to track down a problem with a very simple sql insert
> statement.
> INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM
> tblRecordingId
> This statement has recently begun to cause timeouts (but not all the
> time). When it does work, I get this plan:
> Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
> SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
> [tblRecordingId].[AccountId]=RaiseIfNull(82440)))
> |--Top(1)
> |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
> |--Stream
> Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
> |--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
> CPU=16, Reads=121, Writes=0, Duration=10860.
> When it causes a timeout, there's no execution plan in the profiler.
> CPU=15, Reads=40, Writes=0, Duration=40000.
> tblRecordingId contains 2 int columns and 590 rows. There are no
> indexes or keys.
> I did add deadlock to the profiler but didn't get any entries. The
> server is not doing much other work (cpu usage is very low.)
> I also tried it in the query analyzer. The query usually executes in
> less than a second, but sometimes it takes more than a minute.
>
> Any ideas on what the problem is or hints on what to search for?
>
> Peter
Tibor is correct. The table scan is the problem. Do you not have an
index on the RecordingID column? If not, you need to add one. Also, are
you using the MAX command to return the next key value for inserting? If
so, you really need a unique index on the column and the statement may
not guarantee unique results. I would recommend that you either use an
identity column to have SQL Server auto-generate the next key value or
you use an another table that holds the next value and update it and
grab the key when needed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||[posted and mailed, vnligen svara i nys
Peter Laan (plnews2000@.yahoo.se) writes:
> When it causes a timeout, there's no execution plan in the profiler.
> CPU=15, Reads=40, Writes=0, Duration=40000.
This is because the timeout is something that occurs in the client that
causes it to cancel the batch, and the plan event is not generated until
the query completes.
Beware that when you get command timeouts, you must issue something
like "IF @.@.trancount > 0 ROLLBACK TRANSACTION", as any transaction
started by the batch is not rolled back automatically.
Or even better, set the command timeout to 0 which means wait forever.
Does make life simpler.

> tblRecordingId contains 2 int columns and 590 rows. There are no indexes
> or keys.
As Tibor and David said, for this type of query you must have an index.

> I did add deadlock to the profiler but didn't get any entries.
A deadlock is not a timeout. That's when two processes are waiting for
each to other release resources. As Tibor and David said, you probably
had a blocking scenario.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks to all who replied! As you correctly guessed it was a blocking
problem.
Petersql

No ability to create maintanance plan in sql server 2005

Hello,
i try to create a new maintenance plan in the sql management studio.
i tried it with windows xp (x86, german), 2003 enterprise / standard (both
x64 and english), sql server developer x86/x64 (english and german),
enterprise x64 (english).
to create it i use the wizard, every time and constellation ends with the
same error:
steps:
1) select windows authentication (same with sa in sql authentication)
2) check database integrity (or any other job)
3) select database master (or any other)
4) not scheduled (no other effect if scheduled)
5) no reporting (no other effect if reported)
6) Finish
Error:
Maintenance Plan Wizard Progress
- Creating maintenance plan "MaintenancePlan" (Error)
Messages
* Create maintenance plan failed.
--
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "MaintenancePlan" (Stopped)
Has anyone any idea about thies?I'm not sure but perhaps you get this error if you didn't install Integration Services...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorcerer" <Sorcerer@.discussions.microsoft.com> wrote in message
news:D2C35CAD-1CE9-4F7F-9FB0-DDB6BABD670E@.microsoft.com...
> Hello,
> i try to create a new maintenance plan in the sql management studio.
> i tried it with windows xp (x86, german), 2003 enterprise / standard (both
> x64 and english), sql server developer x86/x64 (english and german),
> enterprise x64 (english).
> to create it i use the wizard, every time and constellation ends with the
> same error:
> steps:
> 1) select windows authentication (same with sa in sql authentication)
> 2) check database integrity (or any other job)
> 3) select database master (or any other)
> 4) not scheduled (no other effect if scheduled)
> 5) no reporting (no other effect if reported)
> 6) Finish
> Error:
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "MaintenancePlan" (Error)
> Messages
> * Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "MaintenancePlan" (Stopped)
> Has anyone any idea about thies?
>|||thank you, this seems to be the solution!
"Tibor Karaszi" schrieb:
> I'm not sure but perhaps you get this error if you didn't install Integration Services...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sorcerer" <Sorcerer@.discussions.microsoft.com> wrote in message
> news:D2C35CAD-1CE9-4F7F-9FB0-DDB6BABD670E@.microsoft.com...
> > Hello,
> >
> > i try to create a new maintenance plan in the sql management studio.
> > i tried it with windows xp (x86, german), 2003 enterprise / standard (both
> > x64 and english), sql server developer x86/x64 (english and german),
> > enterprise x64 (english).
> > to create it i use the wizard, every time and constellation ends with the
> > same error:
> >
> > steps:
> > 1) select windows authentication (same with sa in sql authentication)
> > 2) check database integrity (or any other job)
> > 3) select database master (or any other)
> > 4) not scheduled (no other effect if scheduled)
> > 5) no reporting (no other effect if reported)
> > 6) Finish
> >
> > Error:
> > Maintenance Plan Wizard Progress
> > - Creating maintenance plan "MaintenancePlan" (Error)
> > Messages
> > * Create maintenance plan failed.
> > --
> > ADDITIONAL INFORMATION:
> > Create failed for JobStep 'Subplan'.
> > (Microsoft.SqlServer.MaintenancePlanTasks)
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> > --
> > An exception occurred while executing a Transact-SQL statement or batch.
> > (Microsoft.SqlServer.ConnectionInfo)
> > --
> > The specified '@.subsystem' is invalid (valid values are returned by
> > sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
> > - Adding tasks to the maintenance plan (Stopped)
> > - Adding scheduling options (Stopped)
> > - Adding reporting options (Stopped)
> > - Saving maintenance plan "MaintenancePlan" (Stopped)
> >
> > Has anyone any idea about thies?
> >
>
>sql

No ability to create maintanance plan in sql server 2005

Hello,
i try to create a new maintenance plan in the sql management studio.
i tried it with Windows XP (x86, german), 2003 enterprise / standard (both
x64 and english), sql server developer x86/x64 (english and german),
enterprise x64 (english).
to create it i use the wizard, every time and constellation ends with the
same error:
steps:
1) select windows authentication (same with sa in sql authentication)
2) check database integrity (or any other job)
3) select database master (or any other)
4) not scheduled (no other effect if scheduled)
5) no reporting (no other effect if reported)
6) Finish
Error:
Maintenance Plan Wizard Progress
- Creating maintenance plan "MaintenancePlan" (Error)
Messages
* Create maintenance plan failed.
--
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?Prod...ep&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?Prod...34&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "MaintenancePlan" (Stopped)
Has anyone any idea about thies?I'm not sure but perhaps you get this error if you didn't install Integratio
n Services...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorcerer" <Sorcerer@.discussions.microsoft.com> wrote in message
news:D2C35CAD-1CE9-4F7F-9FB0-DDB6BABD670E@.microsoft.com...
> Hello,
> i try to create a new maintenance plan in the sql management studio.
> i tried it with Windows XP (x86, german), 2003 enterprise / standard (both
> x64 and english), sql server developer x86/x64 (english and german),
> enterprise x64 (english).
> to create it i use the wizard, every time and constellation ends with the
> same error:
> steps:
> 1) select windows authentication (same with sa in sql authentication)
> 2) check database integrity (or any other job)
> 3) select database master (or any other)
> 4) not scheduled (no other effect if scheduled)
> 5) no reporting (no other effect if reported)
> 6) Finish
> Error:
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "MaintenancePlan" (Error)
> Messages
> * Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...34&LinkId=20476
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "MaintenancePlan" (Stopped)
> Has anyone any idea about thies?
>|||thank you, this seems to be the solution!
"Tibor Karaszi" schrieb:

> I'm not sure but perhaps you get this error if you didn't install Integrat
ion Services...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sorcerer" <Sorcerer@.discussions.microsoft.com> wrote in message
> news:D2C35CAD-1CE9-4F7F-9FB0-DDB6BABD670E@.microsoft.com...
>
>

Nightly Maintenance Backup Plan fails -- no trace of why

We have been running a backup plan (created using the sql 2005 Maintenance wizard) for some time. It failed last night but all databases seemed to be backed up.

I looked at the ERRORLOG in <mssql2005>\mssql.1\log as well as

"Servname DB Backup Plan_20070118184358.txt"

but found no errors. Where else can I look to see what went wrong?

TIA,

barkingdog

You can also check the logs of the Sql Server Agent job corresponding to the maintenance plan.

Thanks,

Kuntal

|||

>>> check the logs of the Sql Server Agent job

Where are these located? (Are you referring to SQLAgent.OUT? It had nothing of interest.)

Barkingdog

|||

In Sql 2005, for every maintenance plan an Sql Agent job is created by the same name. You can view its logs as Sql Server Agent -> Jobs -> select the job & right click to View History.

Hope that helps

Kuntal

|||

What was the error under the scheduled job for this maintenance plan?

Also try to run thsi manually from SSMS to see the execution, also ensure the SQLAgent account has required privileges to run this job. ALso check what changes have been since last time when this job was running succesfsully.

Newbie-shrink transaction log files

Hi. I know this is an often-discussed topic, but I hope you can clear up
some confusion for me. I set up a SQL Server 2005 maintenance plan to back
up my databases to disk (we also backup to tape using Backup Exec). My goals
in backing up to disk are 1) to have a couple days of backups on disk in case
I need to do quick restores, and 2) to reduce the size of transaction log
files. In the maint plan, I selected the options to shrink the databases,
backup databases (full), and backup transaction logs. The problem is that
when I executed the maint plan, it didn't reduce the size of the transaction
log physical files which are growing.
I grabbed this SQL code from the Internet, and it works to shrink the tran
log file size:
USE mydb
BACKUP LOG mydb WITH TRUNCATEONLY
DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
GO
I want to automate this task. I don't want to have to edit the above script
every time I create or drop a database. I know I can just have a SQL Agent
job run the script, BUT isn't the maintenance job supposed to shrink tran log
files as part of the "Backup Database (Full)" and Backup Database
"(Transaction Log)" options?
By the way, is the BACKUP LOG statement required to precede the DBCC
SHRINKFILE statement?
Thanks for your help.
Hi Coop,
I see a couple of problems with your request. First, do not shrink your
database and transaction log files periodically. When you shrink a file, the
next thing SQL Server will do is to expand these files to do its work, and
shrinking and expanding these files periodically could be a performance
problem in addition to creating more disk fragmentation that probably will
also affect the performance of your database. My recommendation here is just
schedule full database and transaction log backups. You can still shrink the
files manually once in a while only if you need it.
Second, you want to use TRUNCATE_ONLY which truncates the log without really
doing a backup. The problem here is that you are not doing a transaction log
backup and you are breaking the log chain. I recommend here that you see the
SQL Server documentation (BOL) and understand the SQL Server recovery models
simple, bulk-logged and full. If you really need to use TRUNCATE_ONLY better
change the recovery model to simple.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Coop" wrote:

> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to back
> up my databases to disk (we also backup to tape using Backup Exec). My goals
> in backing up to disk are 1) to have a couple days of backups on disk in case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above script
> every time I create or drop a database. I know I can just have a SQL Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.
|||Coop
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Coop" <Coop@.discussions.microsoft.com> wrote in message
news:91C8FA70-23CF-4D9A-8879-93EC8EDAEF71@.microsoft.com...
> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to
> back
> up my databases to disk (we also backup to tape using Backup Exec). My
> goals
> in backing up to disk are 1) to have a couple days of backups on disk in
> case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the
> transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above
> script
> every time I create or drop a database. I know I can just have a SQL
> Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran
> log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.

Newbie-shrink transaction log files

Hi. I know this is an often-discussed topic, but I hope you can clear up
some confusion for me. I set up a SQL Server 2005 maintenance plan to back
up my databases to disk (we also backup to tape using Backup Exec). My goal
s
in backing up to disk are 1) to have a couple days of backups on disk in cas
e
I need to do quick restores, and 2) to reduce the size of transaction log
files. In the maint plan, I selected the options to shrink the databases,
backup databases (full), and backup transaction logs. The problem is that
when I executed the maint plan, it didn't reduce the size of the transaction
log physical files which are growing.
I grabbed this SQL code from the Internet, and it works to shrink the tran
log file size:
USE mydb
BACKUP LOG mydb WITH TRUNCATEONLY
DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
GO
I want to automate this task. I don't want to have to edit the above script
every time I create or drop a database. I know I can just have a SQL Agent
job run the script, BUT isn't the maintenance job supposed to shrink tran lo
g
files as part of the "Backup Database (Full)" and Backup Database
"(Transaction Log)" options?
By the way, is the BACKUP LOG statement required to precede the DBCC
SHRINKFILE statement?
Thanks for your help.Hi Coop,
I see a couple of problems with your request. First, do not shrink your
database and transaction log files periodically. When you shrink a file, the
next thing SQL Server will do is to expand these files to do its work, and
shrinking and expanding these files periodically could be a performance
problem in addition to creating more disk fragmentation that probably will
also affect the performance of your database. My recommendation here is just
schedule full database and transaction log backups. You can still shrink the
files manually once in a while only if you need it.
Second, you want to use TRUNCATE_ONLY which truncates the log without really
doing a backup. The problem here is that you are not doing a transaction log
backup and you are breaking the log chain. I recommend here that you see the
SQL Server documentation (BOL) and understand the SQL Server recovery models
simple, bulk-logged and full. If you really need to use TRUNCATE_ONLY better
change the recovery model to simple.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Coop" wrote:

> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to bac
k
> up my databases to disk (we also backup to tape using Backup Exec). My go
als
> in backing up to disk are 1) to have a couple days of backups on disk in c
ase
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the transacti
on
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above scri
pt
> every time I create or drop a database. I know I can just have a SQL Agen
t
> job run the script, BUT isn't the maintenance job supposed to shrink tran
log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.|||Coop
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Coop" <Coop@.discussions.microsoft.com> wrote in message
news:91C8FA70-23CF-4D9A-8879-93EC8EDAEF71@.microsoft.com...
> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to
> back
> up my databases to disk (we also backup to tape using Backup Exec). My
> goals
> in backing up to disk are 1) to have a couple days of backups on disk in
> case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the
> transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above
> script
> every time I create or drop a database. I know I can just have a SQL
> Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran
> log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.

Newbie-shrink transaction log files

Hi. I know this is an often-discussed topic, but I hope you can clear up
some confusion for me. I set up a SQL Server 2005 maintenance plan to back
up my databases to disk (we also backup to tape using Backup Exec). My goals
in backing up to disk are 1) to have a couple days of backups on disk in case
I need to do quick restores, and 2) to reduce the size of transaction log
files. In the maint plan, I selected the options to shrink the databases,
backup databases (full), and backup transaction logs. The problem is that
when I executed the maint plan, it didn't reduce the size of the transaction
log physical files which are growing.
I grabbed this SQL code from the Internet, and it works to shrink the tran
log file size:
USE mydb
BACKUP LOG mydb WITH TRUNCATEONLY
DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
GO
I want to automate this task. I don't want to have to edit the above script
every time I create or drop a database. I know I can just have a SQL Agent
job run the script, BUT isn't the maintenance job supposed to shrink tran log
files as part of the "Backup Database (Full)" and Backup Database
"(Transaction Log)" options?
By the way, is the BACKUP LOG statement required to precede the DBCC
SHRINKFILE statement?
Thanks for your help.Hi Coop,
I see a couple of problems with your request. First, do not shrink your
database and transaction log files periodically. When you shrink a file, the
next thing SQL Server will do is to expand these files to do its work, and
shrinking and expanding these files periodically could be a performance
problem in addition to creating more disk fragmentation that probably will
also affect the performance of your database. My recommendation here is just
schedule full database and transaction log backups. You can still shrink the
files manually once in a while only if you need it.
Second, you want to use TRUNCATE_ONLY which truncates the log without really
doing a backup. The problem here is that you are not doing a transaction log
backup and you are breaking the log chain. I recommend here that you see the
SQL Server documentation (BOL) and understand the SQL Server recovery models
simple, bulk-logged and full. If you really need to use TRUNCATE_ONLY better
change the recovery model to simple.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Coop" wrote:
> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to back
> up my databases to disk (we also backup to tape using Backup Exec). My goals
> in backing up to disk are 1) to have a couple days of backups on disk in case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above script
> every time I create or drop a database. I know I can just have a SQL Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.|||Coop
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Coop" <Coop@.discussions.microsoft.com> wrote in message
news:91C8FA70-23CF-4D9A-8879-93EC8EDAEF71@.microsoft.com...
> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to
> back
> up my databases to disk (we also backup to tape using Backup Exec). My
> goals
> in backing up to disk are 1) to have a couple days of backups on disk in
> case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the
> transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above
> script
> every time I create or drop a database. I know I can just have a SQL
> Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran
> log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.