The database is not even 10MB, but the Transaction log is over 40GB!
The recovery model was set to Full (I have set it to simple now) and
have tried performing a full backup with the "Remove inactive entries
from transaction log" checked.
Any advice on how to make the transaction log behave? Thes steps above
were the ones I already new.
Some info at 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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"hobbzilla" <hobbzilla@.hotmail.com> wrote in message
news:1133998771.980365.222680@.g49g2000cwa.googlegr oups.com...
> The database is not even 10MB, but the Transaction log is over 40GB!
> The recovery model was set to Full (I have set it to simple now) and
> have tried performing a full backup with the "Remove inactive entries
> from transaction log" checked.
> Any advice on how to make the transaction log behave? Thes steps above
> were the ones I already new.
>
|||Running
DBCC SHRINKFILE(databasename_log, 2)
cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
after a maintenence plan is run doing a full backup of the database?
|||hobbzilla wrote:
> Running
> DBCC SHRINKFILE(databasename_log, 2)
> cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
> after a maintenence plan is run doing a full backup of the database?
>
No. A full backup will only backup the file but it will preserve the
unused space. It's also the normal procedure that you don't shrink the
file unless there's a specific reason for why it has grown to that size.
If it's due to normal production, the server will just have to use
resources to grow the file again.
Regards
Steen
Showing posts with label model. Show all posts
Showing posts with label model. Show all posts
Wednesday, March 28, 2012
Help! Transaction log is 40GB
The database is not even 10MB, but the Transaction log is over 40GB!
The recovery model was set to Full (I have set it to simple now) and
have tried performing a full backup with the "Remove inactive entries
from transaction log" checked.
Any advice on how to make the transaction log behave? Thes steps above
were the ones I already new.Some info at 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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"hobbzilla" <hobbzilla@.hotmail.com> wrote in message
news:1133998771.980365.222680@.g49g2000cwa.googlegroups.com...
> The database is not even 10MB, but the Transaction log is over 40GB!
> The recovery model was set to Full (I have set it to simple now) and
> have tried performing a full backup with the "Remove inactive entries
> from transaction log" checked.
> Any advice on how to make the transaction log behave? Thes steps above
> were the ones I already new.
>|||Running
DBCC SHRINKFILE(databasename_log, 2)
cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
after a maintenence plan is run doing a full backup of the database?|||hobbzilla wrote:
> Running
> DBCC SHRINKFILE(databasename_log, 2)
> cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
> after a maintenence plan is run doing a full backup of the database?
>
No. A full backup will only backup the file but it will preserve the
unused space. It's also the normal procedure that you don't shrink the
file unless there's a specific reason for why it has grown to that size.
If it's due to normal production, the server will just have to use
resources to grow the file again.
Regards
Steen
The recovery model was set to Full (I have set it to simple now) and
have tried performing a full backup with the "Remove inactive entries
from transaction log" checked.
Any advice on how to make the transaction log behave? Thes steps above
were the ones I already new.Some info at 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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"hobbzilla" <hobbzilla@.hotmail.com> wrote in message
news:1133998771.980365.222680@.g49g2000cwa.googlegroups.com...
> The database is not even 10MB, but the Transaction log is over 40GB!
> The recovery model was set to Full (I have set it to simple now) and
> have tried performing a full backup with the "Remove inactive entries
> from transaction log" checked.
> Any advice on how to make the transaction log behave? Thes steps above
> were the ones I already new.
>|||Running
DBCC SHRINKFILE(databasename_log, 2)
cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
after a maintenence plan is run doing a full backup of the database?|||hobbzilla wrote:
> Running
> DBCC SHRINKFILE(databasename_log, 2)
> cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
> after a maintenence plan is run doing a full backup of the database?
>
No. A full backup will only backup the file but it will preserve the
unused space. It's also the normal procedure that you don't shrink the
file unless there's a specific reason for why it has grown to that size.
If it's due to normal production, the server will just have to use
resources to grow the file again.
Regards
Steen
Help! Transaction log is 40GB
The database is not even 10MB, but the Transaction log is over 40GB!
The recovery model was set to Full (I have set it to simple now) and
have tried performing a full backup with the "Remove inactive entries
from transaction log" checked.
Any advice on how to make the transaction log behave? Thes steps above
were the ones I already new.Some info at 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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"hobbzilla" <hobbzilla@.hotmail.com> wrote in message
news:1133998771.980365.222680@.g49g2000cwa.googlegroups.com...
> The database is not even 10MB, but the Transaction log is over 40GB!
> The recovery model was set to Full (I have set it to simple now) and
> have tried performing a full backup with the "Remove inactive entries
> from transaction log" checked.
> Any advice on how to make the transaction log behave? Thes steps above
> were the ones I already new.
>|||Running
DBCC SHRINKFILE(databasename_log, 2)
cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
after a maintenence plan is run doing a full backup of the database?|||hobbzilla wrote:
> Running
> DBCC SHRINKFILE(databasename_log, 2)
> cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
> after a maintenence plan is run doing a full backup of the database?
>
No. A full backup will only backup the file but it will preserve the
unused space. It's also the normal procedure that you don't shrink the
file unless there's a specific reason for why it has grown to that size.
If it's due to normal production, the server will just have to use
resources to grow the file again.
Regards
Steen
The recovery model was set to Full (I have set it to simple now) and
have tried performing a full backup with the "Remove inactive entries
from transaction log" checked.
Any advice on how to make the transaction log behave? Thes steps above
were the ones I already new.Some info at 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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"hobbzilla" <hobbzilla@.hotmail.com> wrote in message
news:1133998771.980365.222680@.g49g2000cwa.googlegroups.com...
> The database is not even 10MB, but the Transaction log is over 40GB!
> The recovery model was set to Full (I have set it to simple now) and
> have tried performing a full backup with the "Remove inactive entries
> from transaction log" checked.
> Any advice on how to make the transaction log behave? Thes steps above
> were the ones I already new.
>|||Running
DBCC SHRINKFILE(databasename_log, 2)
cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
after a maintenence plan is run doing a full backup of the database?|||hobbzilla wrote:
> Running
> DBCC SHRINKFILE(databasename_log, 2)
> cleaned it up to 2MB from 40GB. Shouldn't this automatically happen
> after a maintenence plan is run doing a full backup of the database?
>
No. A full backup will only backup the file but it will preserve the
unused space. It's also the normal procedure that you don't shrink the
file unless there's a specific reason for why it has grown to that size.
If it's due to normal production, the server will just have to use
resources to grow the file again.
Regards
Steen
Friday, March 23, 2012
Help! Report Model Project doesn't like my primary key
I'm creating a report model in VS2005 I've created my data source fine and I have selected all the tables I want in the report model data view.
The problem is that for one of the tables it is refusing to acknowledge the promary key. If I try to create the report model it compains that the table doesn't have a primary key.
So I went into SQL Management Studio and checked the table, Lo and behold the primary key is there!!! I tried droping the primary key and recreating it but it still says there is no primary ley on the table.
Any ideas?!?Just did some fiddling and managed to find the problem.
There is a Unique Clustered Index on the table which the problem comes from, with the index it doesn't see the primary key, without it the key suddenly appears
Subscribe to:
Posts (Atom)