DENY permission,SQL server: logs

Showing posts with label logs. Show all posts

Tuesday, March 27, 2012

Deleting Older Files

I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

LystraAs far as the full backup, you can overwrite the old backup file every time you take a full backup. This way you don't need to reserve the space for two full backup file. In the case of log backup, if you don't need to or don't want to backup the log file, just use simple recovery mode. It looks like you are deleting them any way.|||That's the problem it is not deleting the older files, even though I have it check to delete the files.

The database recovery mode is set to full.

Lystra|||Drives are cheap....

And what happens when you need to migrate data around?

Do you have a disaster box?

How odten do you dump the transaction log?

How many dumps do you have now?

How big is the hard drive?|||I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra|||Not to sound like a broken record.

I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra

Wednesday, March 21, 2012

Deleting database backups and logs

New to SQL server. During my 'experimental' phase I created backups plans
to back up databases. Missed the part where the data would be removed after
XX time/days, can I safely delete the old TRN and BAK up file in the backup
folder located in the MSSQL directory directly from Windows Explorer?
The short answer is yes.
Rick Sawtell
MCT, MCSD, MCDBA
<msnews.microsoft.com> wrote in message
news:uZCO4EclEHA.1712@.TK2MSFTNGP09.phx.gbl...
> New to SQL server. During my 'experimental' phase I created backups plans
> to back up databases. Missed the part where the data would be removed
after
> XX time/days, can I safely delete the old TRN and BAK up file in the
backup
> folder located in the MSSQL directory directly from Windows Explorer?
>
|||You can also modify the Maintenance Plan once it has been created. In SQL
Enterprise Manager, open the "Maintenance" node. Select Maintenance Plans,
then double-click the maintenance plan that you created. In here you can
modify the properties for the maintenence plan (including the property to
delete old backups). However the answer to your question is - yes you can
delete the files manually through Explorer. These are backup files and SQL
does not hold a lock on these files once it has completed writing them out.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Deleting database backups and logs

New to SQL server. During my 'experimental' phase I created backups plans
to back up databases. Missed the part where the data would be removed after
XX time/days, can I safely delete the old TRN and BAK up file in the backup
folder located in the MSSQL directory directly from Windows Explorer?The short answer is yes.
Rick Sawtell
MCT, MCSD, MCDBA
<msnews.microsoft.com> wrote in message
news:uZCO4EclEHA.1712@.TK2MSFTNGP09.phx.gbl...
> New to SQL server. During my 'experimental' phase I created backups plans
> to back up databases. Missed the part where the data would be removed
after
> XX time/days, can I safely delete the old TRN and BAK up file in the
backup
> folder located in the MSSQL directory directly from Windows Explorer?
>|||You can also modify the Maintenance Plan once it has been created. In SQL
Enterprise Manager, open the "Maintenance" node. Select Maintenance Plans,
then double-click the maintenance plan that you created. In here you can
modify the properties for the maintenence plan (including the property to
delete old backups). However the answer to your question is - yes you can
delete the files manually through Explorer. These are backup files and SQL
does not hold a lock on these files once it has completed writing them out.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Sunday, March 11, 2012

deleting a backup device

I have created two backup devices for a database: 1 for the data and 1 for the logs. I back up transaction logs to the device over the course of a day and then do a differential backup for the data nightly.

Once a week, I do a full backup of the database. After the full backup is done, I want to delete the log file device since it's no longer needed. I've done it before, but haven't in a while and can't remember the syntax in the job step setup. Can someone please refresh me?

I know it's a CmdExec job, but the syntax of the step is eluding my memory.

Thanks for any help.just bumping for assistance.|||re-bumping... any assistance would be greatly appreciated.|||why delete the device? don't you want to leave it there to use it again. just set the backup log init to overwrite the previous log file.

Deleteing large bulks of data

Brief background:

We are using SQL Server 2000, and one of the tables stores user
sessions details (each time our users logs into our system we insert a
new record in the session table, and each time user logs out from our
system we insert another record in the same table).
SESSION_ID is the primary key and it is clustered index.
The system produces 5 million session records/day.

The problem:

Each day we transfer the session data (delta only) to other machine and
we want to delete bulk of ~5 million sessions. This should happend
without any interfering of our customers activity ( in the same time,
we should not block the table - new sessions should be created).

What is the best way to perform such task ?generally truncate table xxx ...will be MUCH faster than delete. Be
aware of some of the logging issues associated with truncate table
before you do this. Search your BOL for "truncate table".

MJKulangara
http://sqladventures.blogspot.com|||Truncating table will delete the entire table, and this is not what we
want. We are looing for a method to delete specific sessions (by
specifying the exact sessions ID's).|||rosherman@.hotmail.com (rosherman@.hotmail.com) writes:
> We are using SQL Server 2000, and one of the tables stores user
> sessions details (each time our users logs into our system we insert a
> new record in the session table, and each time user logs out from our
> system we insert another record in the same table).
> SESSION_ID is the primary key and it is clustered index.
> The system produces 5 million session records/day.
> The problem:
> Each day we transfer the session data (delta only) to other machine and
> we want to delete bulk of ~5 million sessions. This should happend
> without any interfering of our customers activity ( in the same time,
> we should not block the table - new sessions should be created).
> What is the best way to perform such task ?

If I understand this correctly, you want to delete the main bulk of the
five million rows, but keep some of them.

I would consider doing something like:

1) Rename the table.
2) Create a new table with the same schema.
3) Insert the rows you want to keep from the old table to the new table.
4) Drop the old table.

You would need to put 1) and 2) into a transaction. During this
transactions logins would be blocked, but it would be a matter of
centiseconds.

If you can find a method to define a clean cut a head, then you could
consider partitioned views. That is, you would have a set of table
that are united in a view, and a CHECK constraint defining which
intervals that go into which table. Insertions would be into the view.
You would transfer one table a time, and then truncate and finally
redefine it to fit another slot in the future.

--
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|||Thanks. We'll try this method.

Deleted Transaction Logs!

I accidentalty deleted the transaction logs on a DB I had, and now I can get
it up on SQL enterprise, any ideas?
-GaryHi,
Try to attach the database with out the tranaction log using
sp_attach_single_file_db system stored procedure. This may not work since
your database is not detached.
Usage:
sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
file'
If it fails restore the database from the last successful backup.
Thanks
Hari
MCDBA
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Thx Hari,
I have replications running, and it won't let me detach, any ideas on how to
detach this?
-Gary
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Try to attach the database with out the tranaction log using
> sp_attach_single_file_db system stored procedure. This may not work since
> your database is not detached.
> Usage:
> sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
> file'
> If it fails restore the database from the last successful backup.
> Thanks
> Hari
> MCDBA
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > I accidentalty deleted the transaction logs on a DB I had, and now I can
> get
> > it up on SQL enterprise, any ideas?
> >
> > -Gary
> >
> >
>|||is it safe to delete these files? I wont lose core data will I?
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||and if I try to disable publishing, I get errors regarding the database
whose transaction logs are deleted.
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uKy83nMHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Thx Hari,
> I have replications running, and it won't let me detach, any ideas on how
to
> detach this?
> -Gary
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Try to attach the database with out the tranaction log using
> > sp_attach_single_file_db system stored procedure. This may not work
since
> > your database is not detached.
> >
> > Usage:
> >
> > sp_attach_single_file_db 'dbname' , 'physical_name with the path of
MDF
> > file'
> >
> > If it fails restore the database from the last successful backup.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > I accidentalty deleted the transaction logs on a DB I had, and now I
can
> > get
> > > it up on SQL enterprise, any ideas?
> > >
> > > -Gary
> > >
> > >
> >
> >
>|||You will loose any uncomitted data and anything after the last CHECKPOINT,
do you have your recovery model full or simple? If its simple I think you
loose less.
You can also create a new database with the same name, stop sql service and
replace the data files with your original database and then start the
service again. If this didnt work set the database to emergency mode and
then try to copy objects using DTS to a new database.
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> is it safe to delete these files? I wont lose core data will I?
> -Gary
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > I accidentalty deleted the transaction logs on a DB I had, and now I can
> get
> > it up on SQL enterprise, any ideas?
> >
> > -Gary
> >
> >
>|||how can I go to emergency mode?
-Gary
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> You will loose any uncomitted data and anything after the last CHECKPOINT,
> do you have your recovery model full or simple? If its simple I think you
> loose less.
> You can also create a new database with the same name, stop sql service
and
> replace the data files with your original database and then start the
> service again. If this didnt work set the database to emergency mode and
> then try to copy objects using DTS to a new database.
>
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> > is it safe to delete these files? I wont lose core data will I?
> >
> > -Gary
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > I accidentalty deleted the transaction logs on a DB I had, and now I
can
> > get
> > > it up on SQL enterprise, any ideas?
> > >
> > > -Gary
> > >
> > >
> >
> >
>|||UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uhpeyhNHEHA.2128@.TK2MSFTNGP11.phx.gbl...
> how can I go to emergency mode?
> -Gary
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> > You will loose any uncomitted data and anything after the last
CHECKPOINT,
> > do you have your recovery model full or simple? If its simple I think
you
> > loose less.
> >
> > You can also create a new database with the same name, stop sql service
> and
> > replace the data files with your original database and then start the
> > service again. If this didnt work set the database to emergency mode and
> > then try to copy objects using DTS to a new database.
> >
> >
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> > > is it safe to delete these files? I wont lose core data will I?
> > >
> > > -Gary
> > >
> > > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > > I accidentalty deleted the transaction logs on a DB I had, and now I
> can
> > > get
> > > > it up on SQL enterprise, any ideas?
> > > >
> > > > -Gary
> > > >
> > > >
> > >
> > >
> >
> >
>|||You don't have any of the log files at all?
You're best bet is to open a call with PSS. Depending on the circumstances
PSS may be able to help you recover most of the data. There are a number of
things that you could do on your own, that are not documented. But,
honestly, the PSS call is pretty cheap and doing it on your own can be
tricky. I really think the PSS call is worth the price.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Hi Gary,
Here is a technique suggested by Jasper Smith once. I
documented it for later use.
--
Here's a technique I have used in the past - you need to
change the file/database names as this was for a specific
case :
If you did not detach the database prior to copying it
then you will not be able to attach it easily (probably).
First thing to try is make sure the folder
'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
then create it,copy your MDF there and retry your statement
If that doesn't work then you're in a bit of trouble but
you can try this
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file
locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file
to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the
following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following
(make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you
could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need
them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names
*/
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME
= 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME
= 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the
log file
will be small so it will be worth increasing its
size
Unfortunately your files will be called
fake_Data.MDF and
fake_Log.LDF but you can get round this by
detaching the
database properly and then renaming the files and
reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
Regards
Thirumal|||thanks for the help!
-Gary
"Thirumal" <treddym@.hotmail.nospam.com> wrote in message
news:19c8701c41d14$d4ea68e0$a501280a@.phx.gbl...
> Hi Gary,
> Here is a technique suggested by Jasper Smith once. I
> documented it for later use.
> --
> Here's a technique I have used in the past - you need to
> change the file/database names as this was for a specific
> case :
> If you did not detach the database prior to copying it
> then you will not be able to attach it easily (probably).
> First thing to try is make sure the folder
> 'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
> then create it,copy your MDF there and retry your statement
> If that doesn't work then you're in a bit of trouble but
> you can try this
> 1) Make sure you have a copy of PowerDVD301_2_Data.MDF
> 2) Create a new database called fake (default file
> locations)
> 3) Stop SQL Service
> 4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
> to where fake_Data.MDF used to be and rename the file
> to fake_Data.MDF
> 5) Start SQL Service
> 6) Database fake will appear as suspect in EM
> 7) Open Query Analyser and in master database run the
> following :
> sp_configure 'allow updates',1
> go
> reconfigure with override
> go
> update sysdatabases set
> status=-32768 where dbid=DB_ID('fake')
> go
> sp_configure 'allow updates',0
> go
> reconfigure with override
> go
> This will put the database in emergency recovery mode
> 8) Stop SQL Service
> 9) Delete the fake_Log.LDF file
> 10) Restart SQL Service
> 11) In QA run the following (with correct path for log)
> dbcc rebuild_log('fake','h:\fake_log.ldf')
> go
> dbcc checkdb('fake') -- to check for errors
> go
> 12) Now we need to rename the files, run the following
> (make sure
> there are no connections to it) in Query Analyser
> (At this stage you can actually access the database so you
> could use
> DTS or bcp to move the data to another database .)
> use master
> go
> sp_helpdb 'fake'
> go
> /* Make a note of the names of the files , you will need
> them
> in the next bit of the script to replace datafilename and
> logfilename - it might be that they have the right names
> */
> sp_renamedb 'fake','PowerDVD301'
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='datafilename', NEWNAME
> = 'PowerDVD301_Data')
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='logfilename', NEWNAME
> = 'PowerDVD301_Log')
> go
> dbcc checkdb('PowerDVD301')
> go
> sp_dboption 'PowerDVD301','dbo use only','false'
> go
> use PowerDVD301
> go
> sp_updatestats
> go
> 13) You should now have a working database. However the
> log file
> will be small so it will be worth increasing its
> size
> Unfortunately your files will be called
> fake_Data.MDF and
> fake_Log.LDF but you can get round this by
> detaching the
> database properly and then renaming the files and
> reattaching
> it
> 14) Run the following in QA
> sp_detach_db PowerDVD301
>
> --now rename the files then reattach
> sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
>
> Regards
> Thirumal

Deleted Transaction Logs!

Friday, March 9, 2012

Deleted Log file

Hi,
I have a database that use two log files. I deleted one of the logs going to
database/properties/files select the log file, and remove.
Everything goes well, but now when I go to database/properties/files I still
see the tow log files (the folder and file for one of the logs doesnâ't exist
anymore).
Any idea how to resolve this?
ThanksHi
You don't say which version of SQL Server you are using? Have you tried
sp_helpfile to see what that gives and the
ALTER DATABASE REMOVE FILE logical_file_name
command to see if it can be removed that way?
John
"Tecnica" wrote:
> Hi,
> I have a database that use two log files. I deleted one of the logs going to
> database/properties/files select the log file, and remove.
> Everything goes well, but now when I go to database/properties/files I still
> see the tow log files (the folder and file for one of the logs doesnâ't exist
> anymore).
> Any idea how to resolve this?
> Thanks
>|||Hi John,
in fact i didnÂ´t mencioned that the SQL is version 2005.
The ALTER DATABASE REMOVE FILE logical_file_name doesnÂ´t work because
phisicaly the file does not exist.
If you run SELECT * FROM sys.database_files you see the file mencioned in
one row, but i donÂ´t know if i can modify this system table.
Rui
"John Bell" wrote:
> Hi
> You don't say which version of SQL Server you are using? Have you tried
> sp_helpfile to see what that gives and the
> ALTER DATABASE REMOVE FILE logical_file_name
> command to see if it can be removed that way?
> John
>
> "Tecnica" wrote:
> > Hi,
> > I have a database that use two log files. I deleted one of the logs going to
> > database/properties/files select the log file, and remove.
> > Everything goes well, but now when I go to database/properties/files I still
> > see the tow log files (the folder and file for one of the logs doesnâ't exist
> > anymore).
> > Any idea how to resolve this?
> >
> > Thanks
> >|||Hi
You should not need to modify system catalogs. Do you have the number/text
for the error for the ALTER DATABASE command. Are you using SP1?
John
"Tecnica" wrote:
> Hi John,
> in fact i didnÂ´t mencioned that the SQL is version 2005.
> The ALTER DATABASE REMOVE FILE logical_file_name doesnÂ´t work because
> phisicaly the file does not exist.
> If you run SELECT * FROM sys.database_files you see the file mencioned in
> one row, but i donÂ´t know if i can modify this system table.
> Rui
> "John Bell" wrote:
> > Hi
> >
> > You don't say which version of SQL Server you are using? Have you tried
> > sp_helpfile to see what that gives and the
> > ALTER DATABASE REMOVE FILE logical_file_name
> > command to see if it can be removed that way?
> >
> > John
> >
> >
> > "Tecnica" wrote:
> >
> > > Hi,
> > > I have a database that use two log files. I deleted one of the logs going to
> > > database/properties/files select the log file, and remove.
> > > Everything goes well, but now when I go to database/properties/files I still
> > > see the tow log files (the folder and file for one of the logs doesnâ't exist
> > > anymore).
> > > Any idea how to resolve this?
> > >
> > > Thanks
> > >|||Hi,
My understanding of your issue is that:
Your database had two log files. You manually deleted one of the log files
in SQL Server Management Studio. However when you viewed the database
properties again, you found that the log file was still there.
If I have misunderstood, please let me know.
I performed a test in SQL Server 2005 Enterprise Edition SP1, but
unfortunately I couldn't reproduce your issue. Everything works fine at my
side. I created a database called TMLogDB which has two log files:
TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
manually removed the TMLogDB_log1. Then I clicked the Properties of the
database and found that the log file had been removed.
For further research, I recommend that:
1. Check if this issue occurs only on this database. Please create a test
dabase and see if the same issue exists on it;
2. Completely backup your database, then try using sp_detach_db to detach
the database and sp_attach_db to attach the database with the current log
file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
Server Books Online.
3. Install SQL Server 2005 SP1 if it was not installed on your computer.
4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Charles
tanks for the help. Detach and attach database has solve the problem.
Regards,
Rui Reis
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your database had two log files. You manually deleted one of the log files
> in SQL Server Management Studio. However when you viewed the database
> properties again, you found that the log file was still there.
> If I have misunderstood, please let me know.
> I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> unfortunately I couldn't reproduce your issue. Everything works fine at my
> side. I created a database called TMLogDB which has two log files:
> TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> manually removed the TMLogDB_log1. Then I clicked the Properties of the
> database and found that the log file had been removed.
> For further research, I recommend that:
> 1. Check if this issue occurs only on this database. Please create a test
> dabase and see if the same issue exists on it;
> 2. Completely backup your database, then try using sp_detach_db to detach
> the database and sp_attach_db to attach the database with the current log
> file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> Server Books Online.
> 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Sincerely yours,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hi,
Appreciate your update and response. I am glad to hear that the problem has
been fixed. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi
Can you let us know whether this is SP1 or not?
Thanks
John
"Tecnica" wrote:
> Hi Charles
> tanks for the help. Detach and attach database has solve the problem.
> Regards,
> Rui Reis
> "Charles Wang[MSFT]" wrote:
> > Hi,
> > My understanding of your issue is that:
> > Your database had two log files. You manually deleted one of the log files
> > in SQL Server Management Studio. However when you viewed the database
> > properties again, you found that the log file was still there.
> > If I have misunderstood, please let me know.
> >
> > I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> > unfortunately I couldn't reproduce your issue. Everything works fine at my
> > side. I created a database called TMLogDB which has two log files:
> > TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> > manually removed the TMLogDB_log1. Then I clicked the Properties of the
> > database and found that the log file had been removed.
> >
> > For further research, I recommend that:
> > 1. Check if this issue occurs only on this database. Please create a test
> > dabase and see if the same issue exists on it;
> > 2. Completely backup your database, then try using sp_detach_db to detach
> > the database and sp_attach_db to attach the database with the current log
> > file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> > Server Books Online.
> > 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> > 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> > logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> >
> > If you have any other questions or concerns, please feel free to let me
> > know. It's my pleasure to be of assistance.
> >
> > Sincerely yours,
> > Charles Wang
> > Microsoft Online Community Support
> >
> > ======================================================> > When responding to posts, please "Reply to Group" via
> > your newsreader so that others may learn and benefit
> > from this issue.
> > ======================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > ======================================================> >
> >
> >
> >|||Hi
no, it is not SP1 because i already intaled some time ago. It was detach and
atach that solve the problem.
Thanks again,
Rui Reis
"John Bell" wrote:
> Hi
> Can you let us know whether this is SP1 or not?
> Thanks
> John
> "Tecnica" wrote:
> > Hi Charles
> >
> > tanks for the help. Detach and attach database has solve the problem.
> >
> > Regards,
> > Rui Reis
> >
> > "Charles Wang[MSFT]" wrote:
> >
> > > Hi,
> > > My understanding of your issue is that:
> > > Your database had two log files. You manually deleted one of the log files
> > > in SQL Server Management Studio. However when you viewed the database
> > > properties again, you found that the log file was still there.
> > > If I have misunderstood, please let me know.
> > >
> > > I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> > > unfortunately I couldn't reproduce your issue. Everything works fine at my
> > > side. I created a database called TMLogDB which has two log files:
> > > TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> > > manually removed the TMLogDB_log1. Then I clicked the Properties of the
> > > database and found that the log file had been removed.
> > >
> > > For further research, I recommend that:
> > > 1. Check if this issue occurs only on this database. Please create a test
> > > dabase and see if the same issue exists on it;
> > > 2. Completely backup your database, then try using sp_detach_db to detach
> > > the database and sp_attach_db to attach the database with the current log
> > > file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> > > Server Books Online.
> > > 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> > > 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> > > logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> > >
> > > If you have any other questions or concerns, please feel free to let me
> > > know. It's my pleasure to be of assistance.
> > >
> > > Sincerely yours,
> > > Charles Wang
> > > Microsoft Online Community Support
> > >
> > > ======================================================> > > When responding to posts, please "Reply to Group" via
> > > your newsreader so that others may learn and benefit
> > > from this issue.
> > > ======================================================> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > ======================================================> > >
> > >
> > >
> > >|||Hi
I would strongly recommend that you upgrade to SP1 which does not seem to
have this issue.
John
"Tecnica" wrote:
> Hi
> no, it is not SP1 because i already intaled some time ago. It was detach and
> atach that solve the problem.
> Thanks again,
> Rui Reis
> "John Bell" wrote:
> > Hi
> >
> > Can you let us know whether this is SP1 or not?
> >
> > Thanks
> >
> > John
> >
> > "Tecnica" wrote:
> >
> > > Hi Charles
> > >
> > > tanks for the help. Detach and attach database has solve the problem.
> > >
> > > Regards,
> > > Rui Reis
> > >
> > > "Charles Wang[MSFT]" wrote:
> > >
> > > > Hi,
> > > > My understanding of your issue is that:
> > > > Your database had two log files. You manually deleted one of the log files
> > > > in SQL Server Management Studio. However when you viewed the database
> > > > properties again, you found that the log file was still there.
> > > > If I have misunderstood, please let me know.
> > > >
> > > > I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> > > > unfortunately I couldn't reproduce your issue. Everything works fine at my
> > > > side. I created a database called TMLogDB which has two log files:
> > > > TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> > > > manually removed the TMLogDB_log1. Then I clicked the Properties of the
> > > > database and found that the log file had been removed.
> > > >
> > > > For further research, I recommend that:
> > > > 1. Check if this issue occurs only on this database. Please create a test
> > > > dabase and see if the same issue exists on it;
> > > > 2. Completely backup your database, then try using sp_detach_db to detach
> > > > the database and sp_attach_db to attach the database with the current log
> > > > file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> > > > Server Books Online.
> > > > 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> > > > 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> > > > logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> > > >
> > > > If you have any other questions or concerns, please feel free to let me
> > > > know. It's my pleasure to be of assistance.
> > > >
> > > > Sincerely yours,
> > > > Charles Wang
> > > > Microsoft Online Community Support
> > > >
> > > > ======================================================> > > > When responding to posts, please "Reply to Group" via
> > > > your newsreader so that others may learn and benefit
> > > > from this issue.
> > > > ======================================================> > > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > > ======================================================> > > >
> > > >
> > > >
> > > >|||Hi
I didnâ't explain well. I have already upgrade to SP1 some time ago (I donâ't
remember when).
Thanks
"John Bell" wrote:
> Hi
> I would strongly recommend that you upgrade to SP1 which does not seem to
> have this issue.
> John
> "Tecnica" wrote:
> > Hi
> >
> > no, it is not SP1 because i already intaled some time ago. It was detach and
> > atach that solve the problem.
> >
> > Thanks again,
> > Rui Reis
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Can you let us know whether this is SP1 or not?
> > >
> > > Thanks
> > >
> > > John
> > >
> > > "Tecnica" wrote:
> > >
> > > > Hi Charles
> > > >
> > > > tanks for the help. Detach and attach database has solve the problem.
> > > >
> > > > Regards,
> > > > Rui Reis
> > > >
> > > > "Charles Wang[MSFT]" wrote:
> > > >
> > > > > Hi,
> > > > > My understanding of your issue is that:
> > > > > Your database had two log files. You manually deleted one of the log files
> > > > > in SQL Server Management Studio. However when you viewed the database
> > > > > properties again, you found that the log file was still there.
> > > > > If I have misunderstood, please let me know.
> > > > >
> > > > > I performed a test in SQL Server 2005 Enterprise Edition SP1, but
> > > > > unfortunately I couldn't reproduce your issue. Everything works fine at my
> > > > > side. I created a database called TMLogDB which has two log files:
> > > > > TMLogDB_log and TMLogDB_log1. After creating a table in the database, I
> > > > > manually removed the TMLogDB_log1. Then I clicked the Properties of the
> > > > > database and found that the log file had been removed.
> > > > >
> > > > > For further research, I recommend that:
> > > > > 1. Check if this issue occurs only on this database. Please create a test
> > > > > dabase and see if the same issue exists on it;
> > > > > 2. Completely backup your database, then try using sp_detach_db to detach
> > > > > the database and sp_attach_db to attach the database with the current log
> > > > > file. For the usage of sp_detach_db/sp_attach_db, please refer to SQL
> > > > > Server Books Online.
> > > > > 3. Install SQL Server 2005 SP1 if it was not installed on your computer.
> > > > > 4. If this issue perists, please mail me (changliw@.microsoft.com) sql error
> > > > > logs (C:\Program Files\Microsoft SQL Server\MSSQL\LOG).
> > > > >
> > > > > If you have any other questions or concerns, please feel free to let me
> > > > > know. It's my pleasure to be of assistance.
> > > > >
> > > > > Sincerely yours,
> > > > > Charles Wang
> > > > > Microsoft Online Community Support
> > > > >
> > > > > ======================================================> > > > > When responding to posts, please "Reply to Group" via
> > > > > your newsreader so that others may learn and benefit
> > > > > from this issue.
> > > > > ======================================================> > > > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > > > ======================================================> > > > >
> > > > >
> > > > >
> > > > >

Deleted Log file

Wednesday, March 7, 2012

Deleted "old" transaction logs = (no items) in EM. How to recover?

A mistake was made and "old" transaction logs (and old backups) were
deleted. It appears that a long living transaction was living in one of
those logs. Now the EP shows (no items). The DBs themselves are still
functioning OK, just the EP shell that's not working.

What's the proper resolution?

Thank you in advance,

FBCK(optimistck@.gmail.com) writes:
> A mistake was made and "old" transaction logs (and old backups) were
> deleted. It appears that a long living transaction was living in one of
> those logs. Now the EP shows (no items). The DBs themselves are still
> functioning OK, just the EP shell that's not working.
> What's the proper resolution?

EP?

--
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

Tuesday, March 27, 2012

Deleting Older Files

Wednesday, March 21, 2012

Deleting database backups and logs

Deleting database backups and logs

Sunday, March 11, 2012

deleting a backup device

Deleteing large bulks of data

Deleted Transaction Logs!

Deleted Transaction Logs!

Deleted Transaction Logs!

Friday, March 9, 2012

Deleted Log file

Deleted Log file

Deleted Log file

Wednesday, March 7, 2012

Deleted "old" transaction logs = (no items) in EM. How to recover?

DENY permission,SQL server

Blog Archive

About Me