DENY permission,SQL server: production

Showing posts with label production. Show all posts

Thursday, March 29, 2012

Deleting records to save DISK Space...

Hi,

In my data archiving process , I would end up deleting hunders records from the production databases but would that help me save some DISK space immediately? Should I run some DBCC command to get some disk space ?

if SO ..!! What should I do after deleting the records..??

Thanks
Cheriyan."Always be sure to backup your records ... before you delete them ... "|||Have a look in SQL Book online to this command

DBCC SHRINKDATABASE
( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)|||Kim Tripp presented a good session at the PASS conference a couple weeks ago about removing data using filegroups. If you are a PASS member, you can download the presentation here:

http://ew.sqlpass.org/ew/pass/callpapers/attach/TRIPP-Rolling_Range_For_Print.zip

Anyone can also download her scripts here:

http://www.sqlskills.com/pastConferences.asp

Sunday, March 25, 2012

Deleting log files

Hi,
We have many databases in production on our server. Each are generating log
files which have grown very large. I'd like to delete them & basically start
'growing them' again.
Basically, I want to stop tracing, delete the log files then start tracing
again in order to generate new ones. We have nightly backups & don't need any
point of time restores prior to today.
What are the dangers in this. Should I simply go ahead & do this?
Thanks for any advice on this
AntHi
Please refer to the documentation on Backup in SQL Server:
http://msdn2.microsoft.com/en-us/library/ms175477.aspx
Do no just delete the log files as you may end up with a database that is
not usable and this process is not supported by Microsoft, rather set the
correct recovery mode and/or do regular log backups.
--
Regards
Michel Epprecht [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
> Hi,
> We have many databases in production on our server. Each are generating
> log
> files which have grown very large. I'd like to delete them & basically
> start
> 'growing them' again.
> Basically, I want to stop tracing, delete the log files then start tracing
> again in order to generate new ones. We have nightly backups & don't need
> any
> point of time restores prior to today.
> What are the dangers in this. Should I simply go ahead & do this?
> Thanks for any advice on this
> Ant|||Hello,
Are you talking about the Transaction log file (LDF) or the SQL profiler
output files. If it Transaction log files and if the data is production
you should take the Transaction log backup in regular intervals. Thsi will
keep you your LDF file in control.
If it is trace files you could only trace the required events.
Thanks
Hari
"Michael Epprecht [MSFT]" <michael.epprecht@.online.microsoft.com> wrote in
message news:eOn9K7%23SHHA.920@.TK2MSFTNGP05.phx.gbl...
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> Hi,
>> We have many databases in production on our server. Each are generating
>> log
>> files which have grown very large. I'd like to delete them & basically
>> start
>> 'growing them' again.
>> Basically, I want to stop tracing, delete the log files then start
>> tracing
>> again in order to generate new ones. We have nightly backups & don't need
>> any
>> point of time restores prior to today.
>> What are the dangers in this. Should I simply go ahead & do this?
>> Thanks for any advice on this
>> Ant
>|||Hi Michael,
Thanks for the response,
The problem we are facing is we are running out of data on our drive.
"...and/or do regular log backups."
We backup every evening. Would it then be safe to delete these log files?
How can the data base be left unusable if we delete them? Is it just a
matter of not being able to do a point in time restore or will it have more
dire effects.
If I do go ahead & delete them, will the transaction logs just start from
the point that tracing begins again?
Thanks very much for your help.
Ant
"Michael Epprecht [MSFT]" wrote:
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
> > Hi,
> >
> > We have many databases in production on our server. Each are generating
> > log
> > files which have grown very large. I'd like to delete them & basically
> > start
> > 'growing them' again.
> >
> > Basically, I want to stop tracing, delete the log files then start tracing
> > again in order to generate new ones. We have nightly backups & don't need
> > any
> > point of time restores prior to today.
> >
> > What are the dangers in this. Should I simply go ahead & do this?
> >
> > Thanks for any advice on this
> > Ant
>|||Don't just delete the ldf files. They are an integral part of the database. It's like saying that
you need more space on the hd and want to remove the windows folder. Shrink the file (if really
needed), then do a manual grow to the necessary size. And then either put the database in simple
recovery mode, or do regular transaction log backups. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>> Hi
>> Please refer to the documentation on Backup in SQL Server:
>> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
>> Do no just delete the log files as you may end up with a database that is
>> not usable and this process is not supported by Microsoft, rather set the
>> correct recovery mode and/or do regular log backups.
>> --
>> Regards
>> Michel Epprecht [MSFT]
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> > Hi,
>> >
>> > We have many databases in production on our server. Each are generating
>> > log
>> > files which have grown very large. I'd like to delete them & basically
>> > start
>> > 'growing them' again.
>> >
>> > Basically, I want to stop tracing, delete the log files then start tracing
>> > again in order to generate new ones. We have nightly backups & don't need
>> > any
>> > point of time restores prior to today.
>> >
>> > What are the dangers in this. Should I simply go ahead & do this?
>> >
>> > Thanks for any advice on this
>> > Ant
>>|||> We backup every evening.
What types of backups?. Note that a database backup does not remove
uncommitted transactions from the log so the log files will grow
indefinitely. The only way to remove committed log data is with a LOG
backup or by keeping the database in the SIMPLE recovery model. In the FULL
or BULK_LOGGED model, one normally schedules periodic LOG backups in between
to full backups.
Once you've backed up your logs, you can shrink the log file back to a
reasonable size as Tibor suggested.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>> Hi
>> Please refer to the documentation on Backup in SQL Server:
>> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
>> Do no just delete the log files as you may end up with a database that is
>> not usable and this process is not supported by Microsoft, rather set the
>> correct recovery mode and/or do regular log backups.
>> --
>> Regards
>> Michel Epprecht [MSFT]
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> > Hi,
>> >
>> > We have many databases in production on our server. Each are generating
>> > log
>> > files which have grown very large. I'd like to delete them & basically
>> > start
>> > 'growing them' again.
>> >
>> > Basically, I want to stop tracing, delete the log files then start
>> > tracing
>> > again in order to generate new ones. We have nightly backups & don't
>> > need
>> > any
>> > point of time restores prior to today.
>> >
>> > What are the dangers in this. Should I simply go ahead & do this?
>> >
>> > Thanks for any advice on this
>> > Ant
>>|||Hello,
If your recovery model is FULL or BULK_LOGGED you need to perform
Transaction Log Backup in frequent intervals. This will make sure that LDF
file will not grow.
Follw the below steps:-
1. Schedule a Daily full backup once a day.
2. Schedule a Transaction Log backup every 30 minutes
3. After the completion of daily full database backup you could delete all
the previous transction log backup files
This approach will help you to recover the database fully.
If your current LDF file is huge then use DBCC SHRINKFILE to reduce the
size. Before shrink either truncate the Log or Backup the log.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>> Hi
>> Please refer to the documentation on Backup in SQL Server:
>> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
>> Do no just delete the log files as you may end up with a database that is
>> not usable and this process is not supported by Microsoft, rather set the
>> correct recovery mode and/or do regular log backups.
>> --
>> Regards
>> Michel Epprecht [MSFT]
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> > Hi,
>> >
>> > We have many databases in production on our server. Each are generating
>> > log
>> > files which have grown very large. I'd like to delete them & basically
>> > start
>> > 'growing them' again.
>> >
>> > Basically, I want to stop tracing, delete the log files then start
>> > tracing
>> > again in order to generate new ones. We have nightly backups & don't
>> > need
>> > any
>> > point of time restores prior to today.
>> >
>> > What are the dangers in this. Should I simply go ahead & do this?
>> >
>> > Thanks for any advice on this
>> > Ant
>>

Sunday, March 11, 2012

Deleted table.

Hi all,

I'm struck, i'm working on a production support by mistake i deleted a table without a transaction, is there anyway i could retrieve the records that got deleted. :(

Thanks in advance.

Arun

restore from backup to another database and copy the records over.

Good luck|||

Is there anyway by which i can retrieve it from transaction log.

|||

Possibly, if you haven't backed-up the transaction log since the deletion and if your database's recovery model is not 'Simple'.

One of these tools should be able to help:

http://www.red-gate.com/products/sql_log_rescue/index.htm

http://www.lumigent.com/products/le_sql.html

For future reference, it's a good idea to execute BEGIN TRANSACTION before making manual corrections to Production data - that way you can always ROLLBACK if your code doesn't behave as expected.

Chris

|||If you can get it from a backup, I would highly recommend that. I know it stinks, but it will be the most straightforward (and the most supported :)

Friday, February 17, 2012

Delete or detach a Suspect database

Everything I read about sp_resetstatus says you must restart SQL after. Is
there a way around that (don't want to shut down production). The suspect
db is just an empty db that is not needed. I need to create another db with
the same name.
Thanks.rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Everything I read about sp_resetstatus says you must restart SQL after.
Is there a way around that (don't want to shut down production). The
suspect db is just an empty db that is not needed. I need to create
another db with the same name.

I may be missing something, but what about DROP DATABASE? I made a quick
test on SQL 2000 where I created a database, stopped SQL Server, deleted
the log file to make the database suspect, and restarted SQL Server.
DROP DATABASE worked just fine.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||No queries would work since this database did not exist in
Master.sysdatabases. Not sure why. Attempting to detach from Enterprise
Manager produced an error. Then I simply closed and reopened EM and it was
gone! Odd.

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99B1EF3C598D6Yazorman@.127.0.0.1...

Quote:

Originally Posted by

rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

>Everything I read about sp_resetstatus says you must restart SQL after.
>Is there a way around that (don't want to shut down production). The
>suspect db is just an empty db that is not needed. I need to create
>another db with the same name.

>
I may be missing something, but what about DROP DATABASE? I made a quick
test on SQL 2000 where I created a database, stopped SQL Server, deleted
the log file to make the database suspect, and restarted SQL Server.
DROP DATABASE worked just fine.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

No queries would work since this database did not exist in
Master.sysdatabases. Not sure why. Attempting to detach from Enterprise
Manager produced an error. Then I simply closed and reopened EM and it was
gone! Odd.

The last thing you should believe is a graphical tool. But they usually
offer a Refresh option to handle such situations.

Thursday, March 29, 2012

Deleting records to save DISK Space...

Sunday, March 25, 2012

Deleting log files

Deleting log files

Deleting log files

Sunday, March 11, 2012

Deleted table.

Friday, February 17, 2012

Delete or detach a Suspect database

DENY permission,SQL server

Blog Archive

About Me