MDF Files currupted: multiple

Showing posts with label multiple. Show all posts

Monday, March 26, 2012

Named instances..anything different than default ?

We have always used default instances of SQL Server and wanted to start
considering multiple named instances on one physical host. Anything to watch
out for other than just the name change which goes as
Hostname\NamedInstance1 ?
ThanksHassam
Each instance on the server consumes memory , just be aware.
"Hassan" <hassan@.hotmail.com> wrote in message
news:u1rPioitHHA.4688@.TK2MSFTNGP05.phx.gbl...
> We have always used default instances of SQL Server and wanted to start
> considering multiple named instances on one physical host. Anything to
> watch out for other than just the name change which goes as
> Hostname\NamedInstance1 ?
> Thanks
>|||Hello,
As well as each installed named instance require seperate SQL Server
licenses.
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u2sfZ8itHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hassam
> Each instance on the server consumes memory , just be aware.
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u1rPioitHHA.4688@.TK2MSFTNGP05.phx.gbl...
>> We have always used default instances of SQL Server and wanted to start
>> considering multiple named instances on one physical host. Anything to
>> watch out for other than just the name change which goes as
>> Hostname\NamedInstance1 ?
>> Thanks
>|||> As well as each installed named instance require seperate SQL Server
> licenses.
I believe licensing varies by both SQL Server version and edition. Separate
licenses are needed in some cases but not others.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uS1F4hjtHHA.4572@.TK2MSFTNGP02.phx.gbl...
> Hello,
> As well as each installed named instance require seperate SQL Server
> licenses.
> Thanks
> Hari
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u2sfZ8itHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hassam
>> Each instance on the server consumes memory , just be aware.
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:u1rPioitHHA.4688@.TK2MSFTNGP05.phx.gbl...
>> We have always used default instances of SQL Server and wanted to start
>> considering multiple named instances on one physical host. Anything to
>> watch out for other than just the name change which goes as
>> Hostname\NamedInstance1 ?
>> Thanks
>>
>|||For SQL 2005 you don't need licenses for separate instances. You do
need one license for the machine though.
Dan Guzman wrote:
>> As well as each installed named instance require seperate SQL Server
>> licenses.
> I believe licensing varies by both SQL Server version and edition.
> Separate licenses are needed in some cases but not others.
>|||Is that true ? That would be cool.
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:O7EZ59ntHHA.1728@.TK2MSFTNGP06.phx.gbl...
> For SQL 2005 you don't need licenses for separate instances. You do need
> one license for the machine though.
>
> Dan Guzman wrote:
>> As well as each installed named instance require seperate SQL Server
>> licenses.
>> I believe licensing varies by both SQL Server version and edition.
>> Separate licenses are needed in some cases but not others.|||Check the readme and licensing info that comes with SQL 2005. I also
checked with our MS rep about it.
Hassan wrote:
> Is that true ? That would be cool.
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:O7EZ59ntHHA.1728@.TK2MSFTNGP06.phx.gbl...
>> For SQL 2005 you don't need licenses for separate instances. You do need
>> one license for the machine though.
>>
>> Dan Guzman wrote:
>> As well as each installed named instance require seperate SQL Server
>> licenses.
>> I believe licensing varies by both SQL Server version and edition.
>> Separate licenses are needed in some cases but not others.
>

Named instances..anything different than default ?

Wednesday, March 21, 2012

Name multiple sheets in excel export?

I have a report that when exported to Excel creates multiple sheets. Im
doing this on purpose. I wanted know if anyone new a way to
automatically name the Sheets in the Excel export? I wanted to name
them from a field on the report.I don't believe this is possible in the current versions of Reporting
Services. It seems like a functionality that would have a great deal
of value for reporting purposes, so hopefully MS will include this
feature in a future service pack.
pichula wrote:
> I have a report that when exported to Excel creates multiple sheets. Im
> doing this on purpose. I wanted know if anyone new a way to
> automatically name the Sheets in the Excel export? I wanted to name
> them from a field on the report.|||I'm pretty sure you can't do this. I'm very sure you couldn't do it with RS
2000 (tried to find info about it myself a while ago), and I don't think you
can do it in RS 2005 either.
A pity, really, as it would be very usefull!
Kaisa M. Lindahl Lervik
"pichula" <kansas4451@.yahoo.com> wrote in message
news:1162321874.757668.47520@.e64g2000cwd.googlegroups.com...
>I have a report that when exported to Excel creates multiple sheets. Im
> doing this on purpose. I wanted know if anyone new a way to
> automatically name the Sheets in the Excel export? I wanted to name
> them from a field on the report.
>|||Thanks for your replies. If this is not possible in RS 2005, does
anybody know about any workaround to this problem? Maybe using Excel
macros, etc.
elPablo
Kaisa M. Lindahl Lervik wrote:
> I'm pretty sure you can't do this. I'm very sure you couldn't do it with RS
> 2000 (tried to find info about it myself a while ago), and I don't think you
> can do it in RS 2005 either.
> A pity, really, as it would be very usefull!
> Kaisa M. Lindahl Lervik
>
> "pichula" <kansas4451@.yahoo.com> wrote in message
> news:1162321874.757668.47520@.e64g2000cwd.googlegroups.com...
> >I have a report that when exported to Excel creates multiple sheets. Im
> > doing this on purpose. I wanted know if anyone new a way to
> > automatically name the Sheets in the Excel export? I wanted to name
> > them from a field on the report.
> >|||Have you tried to write a post process that runs on your excel sheet and
renames the exported sheets?
=-Chris
"pichula" <kansas4451@.yahoo.com> wrote in message
news:1162321874.757668.47520@.e64g2000cwd.googlegroups.com...
>I have a report that when exported to Excel creates multiple sheets. Im
> doing this on purpose. I wanted know if anyone new a way to
> automatically name the Sheets in the Excel export? I wanted to name
> them from a field on the report.
>

Monday, February 20, 2012

My log file is growing huge! Why? Help ASAP! please

Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
seeing this multiple times each day:
---
Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled by
user or timed out after __ milliseconds. Use ALTER DATABASE to set a smaller
FileGrowth value for this file or to explicitly set a new file size.
---
(file growth = 10%)
At the same time, applications are experiencing timeouts on reads/writes,
which is a major problem, probably (I'm guessing) because this is causing a
database lock. This particular DB is about 3 GB and the log file is about
30 GB. The log file was only 1 mb before I did the upgrade. I have no
idea why this is happening. I am not using replication or log shipping or
anything like that.
a) what gets stored in the log file?
b) why is mine growing out of control?
c) how do I stop the growth?
d) how do I get the log file back to a small size again?Do a backup of the transaction log and then shrink the file.
Ben Nevarez, MCDBA, OCP
Database Administrator
"D. Patrick" wrote:
> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
> seeing this multiple times each day:
> ---
> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled by
> user or timed out after __ milliseconds. Use ALTER DATABASE to set a smaller
> FileGrowth value for this file or to explicitly set a new file size.
> ---
> (file growth = 10%)
> At the same time, applications are experiencing timeouts on reads/writes,
> which is a major problem, probably (I'm guessing) because this is causing a
> database lock. This particular DB is about 3 GB and the log file is about
> 30 GB. The log file was only 1 mb before I did the upgrade. I have no
> idea why this is happening. I am not using replication or log shipping or
> anything like that.
> a) what gets stored in the log file?
> b) why is mine growing out of control?
> c) how do I stop the growth?
> d) how do I get the log file back to a small size again?
>
>|||Thanks for the reply, but I don't believe that solves the underlying
problem. Why is it growing? What should look at?
And BOL says that shrinking the file may not actually shrink the physical
file. I am very confused and would appreciate any information or
step-by-step instructions. Thank you.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
> Do a backup of the transaction log and then shrink the file.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "D. Patrick" wrote:
>> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
>> seeing this multiple times each day:
>> ---
>> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled
>> by
>> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
>> smaller
>> FileGrowth value for this file or to explicitly set a new file size.
>> ---
>> (file growth = 10%)
>> At the same time, applications are experiencing timeouts on reads/writes,
>> which is a major problem, probably (I'm guessing) because this is causing
>> a
>> database lock. This particular DB is about 3 GB and the log file is
>> about
>> 30 GB. The log file was only 1 mb before I did the upgrade. I have no
>> idea why this is happening. I am not using replication or log shipping
>> or
>> anything like that.
>> a) what gets stored in the log file?
>> b) why is mine growing out of control?
>> c) how do I stop the growth?
>> d) how do I get the log file back to a small size again?
>>|||A reason for the growth is that your DB is perhaps in Fully logged
recovery mode and maybe it was in simple recovery mode on the old
server
Take a look at this it might help you out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||That is true. I changed that option recently. Does that option really use
10x the space of the database (and more, it seems, by the day)? It seems
that it is useless if it uses 3 GB per day on a 3 GB database. Is there
some other setting that removes log info after each database backup, so that
it will only keep a bit of log data?
Can I change that back to simple while the production DB is running?
I have read BOL and your URL links about shrinking database files. Maybe
I'm just stupid, but I still have no idea what to type into the query window
to get back to a nice little 1 MB log file (instead of the 30 GB and growing
it is at right now). Please help. Thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147461310.310058.75290@.g10g2000cwb.googlegroups.com...
>A reason for the growth is that your DB is perhaps in Fully logged
> recovery mode and maybe it was in simple recovery mode on the old
> server
> Take a look at this it might help you out
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||It is normal for the transaction log file to grow. Are you doing regular
transaction log backups?
Try that first (backup and shrink) and let us know what happen.
Ben Nevarez, MCDBA, OCP
Database Administrator
"D. Patrick" wrote:
> Thanks for the reply, but I don't believe that solves the underlying
> problem. Why is it growing? What should look at?
> And BOL says that shrinking the file may not actually shrink the physical
> file. I am very confused and would appreciate any information or
> step-by-step instructions. Thank you.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
> >
> > Do a backup of the transaction log and then shrink the file.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "D. Patrick" wrote:
> >
> >> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
> >> seeing this multiple times each day:
> >>
> >> ---
> >> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled
> >> by
> >> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
> >> smaller
> >> FileGrowth value for this file or to explicitly set a new file size.
> >> ---
> >> (file growth = 10%)
> >>
> >> At the same time, applications are experiencing timeouts on reads/writes,
> >> which is a major problem, probably (I'm guessing) because this is causing
> >> a
> >> database lock. This particular DB is about 3 GB and the log file is
> >> about
> >> 30 GB. The log file was only 1 mb before I did the upgrade. I have no
> >> idea why this is happening. I am not using replication or log shipping
> >> or
> >> anything like that.
> >>
> >> a) what gets stored in the log file?
> >> b) why is mine growing out of control?
> >> c) how do I stop the growth?
> >> d) how do I get the log file back to a small size again?
> >>
> >>
> >>
>
>|||I have not been backing up the log. Is that the problem? When you do a log
backup, does that automatically then drop the file back to 1 MB again? A
command example that I can type in the query window would greatly help me.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:1CAE345F-A36B-4840-B190-495705186E06@.microsoft.com...
> It is normal for the transaction log file to grow. Are you doing regular
> transaction log backups?
> Try that first (backup and shrink) and let us know what happen.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "D. Patrick" wrote:
>> Thanks for the reply, but I don't believe that solves the underlying
>> problem. Why is it growing? What should look at?
>> And BOL says that shrinking the file may not actually shrink the physical
>> file. I am very confused and would appreciate any information or
>> step-by-step instructions. Thank you.
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
>> >
>> > Do a backup of the transaction log and then shrink the file.
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "D. Patrick" wrote:
>> >
>> >> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
>> >> seeing this multiple times each day:
>> >>
>> >> ---
>> >> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was
>> >> cancelled
>> >> by
>> >> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
>> >> smaller
>> >> FileGrowth value for this file or to explicitly set a new file size.
>> >> ---
>> >> (file growth = 10%)
>> >>
>> >> At the same time, applications are experiencing timeouts on
>> >> reads/writes,
>> >> which is a major problem, probably (I'm guessing) because this is
>> >> causing
>> >> a
>> >> database lock. This particular DB is about 3 GB and the log file is
>> >> about
>> >> 30 GB. The log file was only 1 mb before I did the upgrade. I have
>> >> no
>> >> idea why this is happening. I am not using replication or log
>> >> shipping
>> >> or
>> >> anything like that.
>> >>
>> >> a) what gets stored in the log file?
>> >> b) why is mine growing out of control?
>> >> c) how do I stop the growth?
>> >> d) how do I get the log file back to a small size again?
>> >>
>> >>
>> >>
>>|||backup log with TRUNCATE_ONLY will release space, however you still
need to issue a SHRINK command after that
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Two questions:
Can I do this on the production server without causing live problems?
Should I do this in a script, in the future, after all full database backups
(assuming that I don't want to roll back prior to the full backup moment)?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
> backup log with TRUNCATE_ONLY will release space, however you still
> need to issue a SHRINK command after that
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||My database is similar in size to yours (actually a little larger). I have
SQL Agent backing up the log every 15 minutes with full logging on. This
keeps the log file from growing too large. Also, in the event of a failure,
I am able to restore to the point 15 minutes (sometimes 30) before the
failure. We (OK my boss) decided this was an acceptable trade off vs. the
expense of having a true failover server.
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
> Two questions:
> Can I do this on the production server without causing live problems?
> Should I do this in a script, in the future, after all full database
backups
> (assuming that I don't want to roll back prior to the full backup moment)?
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
> > backup log with TRUNCATE_ONLY will release space, however you still
> > need to issue a SHRINK command after that
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
>|||Would you mind posting the script that is running to back up your log file?
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:uLFGIAgdGHA.4276@.TK2MSFTNGP03.phx.gbl...
> My database is similar in size to yours (actually a little larger). I have
> SQL Agent backing up the log every 15 minutes with full logging on. This
> keeps the log file from growing too large. Also, in the event of a
> failure,
> I am able to restore to the point 15 minutes (sometimes 30) before the
> failure. We (OK my boss) decided this was an acceptable trade off vs. the
> expense of having a true failover server.
> "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
> news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
>> Two questions:
>> Can I do this on the production server without causing live problems?
>> Should I do this in a script, in the future, after all full database
> backups
>> (assuming that I don't want to roll back prior to the full backup
>> moment)?
>>
>> "SQL" <denis.gobo@.gmail.com> wrote in message
>> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
>> > backup log with TRUNCATE_ONLY will release space, however you still
>> > need to issue a SHRINK command after that
>> >
>> > Denis the SQL Menace
>> > http://sqlservercode.blogspot.com/
>> >
>>
>|||It won't make much sense. I used Enterprise Manager and created a DB
Maintenance Plan to do the job. So the script looks like:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
8797A992-C063-43C1-AAC4-5C0302531C89 -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "C:\USB_Drive\SQL_Backup" -DelBkUps 4DAYS -BkExt "TRN"'
Using a maintenance plan is pretty easy for something like this that isn't
too complicated.
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:6369g.1950$uM4.783@.tornado.socal.rr.com...
> Would you mind posting the script that is running to back up your log
file?
>
> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
message
> news:uLFGIAgdGHA.4276@.TK2MSFTNGP03.phx.gbl...
> > My database is similar in size to yours (actually a little larger). I
have
> > SQL Agent backing up the log every 15 minutes with full logging on. This
> > keeps the log file from growing too large. Also, in the event of a
> > failure,
> > I am able to restore to the point 15 minutes (sometimes 30) before the
> > failure. We (OK my boss) decided this was an acceptable trade off vs.
the
> > expense of having a true failover server.
> >
> > "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
> > news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
> >> Two questions:
> >>
> >> Can I do this on the production server without causing live problems?
> >>
> >> Should I do this in a script, in the future, after all full database
> > backups
> >> (assuming that I don't want to roll back prior to the full backup
> >> moment)?
> >>
> >>
> >> "SQL" <denis.gobo@.gmail.com> wrote in message
> >> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
> >> > backup log with TRUNCATE_ONLY will release space, however you still
> >> > need to issue a SHRINK command after that
> >> >
> >> > Denis the SQL Menace
> >> > http://sqlservercode.blogspot.com/
> >> >
> >>
> >>
> >
> >
>|||thank you.
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:eRHv8VgdGHA.564@.TK2MSFTNGP02.phx.gbl...
> It won't make much sense. I used Enterprise Manager and created a DB
> Maintenance Plan to do the job. So the script looks like:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8797A992-C063-43C1-AAC4-5C0302531C89 -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "C:\USB_Drive\SQL_Backup" -DelBkUps 4DAYS -BkExt "TRN"'
> Using a maintenance plan is pretty easy for something like this that isn't
> too complicated.
> "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
> news:6369g.1950$uM4.783@.tornado.socal.rr.com...
>> Would you mind posting the script that is running to back up your log
> file?
>>
>> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
> message
>> news:uLFGIAgdGHA.4276@.TK2MSFTNGP03.phx.gbl...
>> > My database is similar in size to yours (actually a little larger). I
> have
>> > SQL Agent backing up the log every 15 minutes with full logging on.
>> > This
>> > keeps the log file from growing too large. Also, in the event of a
>> > failure,
>> > I am able to restore to the point 15 minutes (sometimes 30) before the
>> > failure. We (OK my boss) decided this was an acceptable trade off vs.
> the
>> > expense of having a true failover server.
>> >
>> > "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
>> > news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
>> >> Two questions:
>> >>
>> >> Can I do this on the production server without causing live problems?
>> >>
>> >> Should I do this in a script, in the future, after all full database
>> > backups
>> >> (assuming that I don't want to roll back prior to the full backup
>> >> moment)?
>> >>
>> >>
>> >> "SQL" <denis.gobo@.gmail.com> wrote in message
>> >> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
>> >> > backup log with TRUNCATE_ONLY will release space, however you still
>> >> > need to issue a SHRINK command after that
>> >> >
>> >> > Denis the SQL Menace
>> >> > http://sqlservercode.blogspot.com/
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Another way to do it is to "detach" the database and then "attach" it back.
This can be done throught Enterprise Manager. Usually one does that as part
of moving a database, but if you just reattach from the same place, it works
fine and shrinks the log file. We went from 10 GB to under 1 MB. I had
experimented with shrink and truncate before and never could get it quite
right, but the detach/attach worked fine. Alternatively, you can use Query
Analyzer and run sp_detach_db and sp_attach_db.|||"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:NB59g.1899$uM4.1049@.tornado.socal.rr.com...
> I have not been backing up the log. Is that the problem?
Yes, that is exactly the problem.
> When you do a log
> backup, does that automatically then drop the file back to 1 MB again?
Not quite, it removes all complete transactions. If you have transactions
that aren't complete, they'll stay in the log until the next backup.
> A
> command example that I can type in the query window would greatly help me.
backup log <dbname> to disk='f:\log_backups\<dbname>_tlog_200605121724.trn'
But your best bet is to schedule a daily or more often backup.
(we do ours every 20 minutes for example so that we're never more than 20
minutes "out of date" in the event of disaster recovery.)
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:1CAE345F-A36B-4840-B190-495705186E06@.microsoft.com...
> >
> > It is normal for the transaction log file to grow. Are you doing regular
> > transaction log backups?
> >
> > Try that first (backup and shrink) and let us know what happen.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "D. Patrick" wrote:
> >
> >> Thanks for the reply, but I don't believe that solves the underlying
> >> problem. Why is it growing? What should look at?
> >>
> >> And BOL says that shrinking the file may not actually shrink the
physical
> >> file. I am very confused and would appreciate any information or
> >> step-by-step instructions. Thank you.
> >>
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
> >> >
> >> > Do a backup of the transaction log and then shrink the file.
> >> >
> >> > Ben Nevarez, MCDBA, OCP
> >> > Database Administrator
> >> >
> >> >
> >> > "D. Patrick" wrote:
> >> >
> >> >> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I
am
> >> >> seeing this multiple times each day:
> >> >>
> >> >> ---
> >> >> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was
> >> >> cancelled
> >> >> by
> >> >> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
> >> >> smaller
> >> >> FileGrowth value for this file or to explicitly set a new file size.
> >> >> ---
> >> >> (file growth = 10%)
> >> >>
> >> >> At the same time, applications are experiencing timeouts on
> >> >> reads/writes,
> >> >> which is a major problem, probably (I'm guessing) because this is
> >> >> causing
> >> >> a
> >> >> database lock. This particular DB is about 3 GB and the log file is
> >> >> about
> >> >> 30 GB. The log file was only 1 mb before I did the upgrade. I
have
> >> >> no
> >> >> idea why this is happening. I am not using replication or log
> >> >> shipping
> >> >> or
> >> >> anything like that.
> >> >>
> >> >> a) what gets stored in the log file?
> >> >> b) why is mine growing out of control?
> >> >> c) how do I stop the growth?
> >> >> d) how do I get the log file back to a small size again?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||"D Shirley" <D Shirley@.discussions.microsoft.com> wrote in message
news:E07A48F1-468A-407E-8FCD-885A72C51297@.microsoft.com...
> Another way to do it is to "detach" the database and then "attach" it
back.
> This can be done throught Enterprise Manager. Usually one does that as
part
> of moving a database, but if you just reattach from the same place, it
works
> fine and shrinks the log file.
Umm, this should NOT work that way unless you're deleting the logfile.
Don't do that. it may work and you could end up with a ruined database.
> We went from 10 GB to under 1 MB. I had
> experimented with shrink and truncate before and never could get it quite
> right, but the detach/attach worked fine. Alternatively, you can use
Query
> Analyzer and run sp_detach_db and sp_attach_db.
>
Also, do NOT shrink and truncate unless you really really don't care about
disaster recovery. (or have very simple needs.)
Shrinking can lead to a fragmented file on the disk and truncating means
you've limited what you can recover.

Monday, March 26, 2012

Named instances..anything different than default ?

Named instances..anything different than default ?

Named instances..anything different than default ?

Wednesday, March 21, 2012

Name multiple sheets in excel export?

Monday, February 20, 2012

My log file is growing huge! Why? Help ASAP! please

My log file is growing huge! Why? Help ASAP! please

MDF Files currupted

Blog Archive

About Me