hot backup: deleting

Showing posts with label deleting. Show all posts

Monday, March 12, 2012

How bad is this??

After detaching a database and deleting the logfile, I use
sp_attach_single_file_db to attach the database and get the following error:
Could not open new database 'Orders'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'F:\'Orders' may be
incorrect.
Is there any way to recover without the log file now?> After detaching a database and deleting the logfile
Can I ask why you would ever do this?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||You have any backups of the database or database files?
"Bryan" <bryan.charlton@.api-wi.com> wrote in message
news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> After detaching a database and deleting the logfile, I use
> sp_attach_single_file_db to attach the database and get the following
error:
> Could not open new database 'Orders'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'F:\'Orders' may be
> incorrect.
> Is there any way to recover without the log file now?
>
>
>
>
>|||Also, is your Resume up to date?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe" <JoeD777@.lycos.com> wrote in message
news:uZ7y3yRZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> You have any backups of the database or database files?
>
> "Bryan" <bryan.charlton@.api-wi.com> wrote in message
> news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> > After detaching a database and deleting the logfile, I use
> > sp_attach_single_file_db to attach the database and get the following
> error:
> >
> > Could not open new database 'Orders'. CREATE DATABASE is aborted.
> > Device activation error. The physical file name 'F:\'Orders' may be
> > incorrect.
> >
> > Is there any way to recover without the log file now?
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>|||Yes, I have a backup that I can restore from. This is my only option I take
it?
"Joe" <JoeD777@.lycos.com> wrote in message
news:uZ7y3yRZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> You have any backups of the database or database files?
>
> "Bryan" <bryan.charlton@.api-wi.com> wrote in message
> news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
>> After detaching a database and deleting the logfile, I use
>> sp_attach_single_file_db to attach the database and get the following
> error:
>> Could not open new database 'Orders'. CREATE DATABASE is aborted.
>> Device activation error. The physical file name 'F:\'Orders' may be
>> incorrect.
>> Is there any way to recover without the log file now?
>>
>>
>>
>>
>>
>|||I have actually done this more times than I can count when moving
development databases from server to server and have never had a problem
(until now).
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQl33yRZEHA.716@.TK2MSFTNGP11.phx.gbl...
>> After detaching a database and deleting the logfile
> Can I ask why you would ever do this?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||> I have actually done this more times than I can count
Move the data and the log file. Or, backup and restore. What you're doing
is quite similar roulette, and you've merely been on a lucky roll until this
spin.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||> Yes, I have a backup that I can restore from. This is my only option I
take
> it?
Or, you can call PSS.|||Isn't it possible that the physical file name really is incorrect, as the
error says? Are you sure it isn't 'F:\'Orders.mdf' instead of 'F:\'Orders'?
You don't need the log file to attach the DB.
-John Oakes
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Omp91KSZEHA.1048@.tk2msftngp13.phx.gbl...
> > I have actually done this more times than I can count
> Move the data and the log file. Or, backup and restore. What you're
doing
> is quite similar roulette, and you've merely been on a lucky roll until
this
> spin.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||theoretically he should have been alright
Inside SQL 2000 "One benefit of using the sp_detach_db procedure is that SQL
Server will know that the database was cleanly shut down, and the log file
does not have to be available to attach the database. SQL will build a new
log file for you. This can be a quick way to shrink a log file that has
become much larger than you would like, because the new log file that
sp_attach_db creates for you will be the minimum size?less than 1 MB. Note
that this trick for shrinking the log will not work if the database has more
than one log file."
But you're right he should have been a bit more careful on a production
server. P45 time!
--
Br,
Mark Broadbent
mcdba , mcse+i
============="Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Omp91KSZEHA.1048@.tk2msftngp13.phx.gbl...
> > I have actually done this more times than I can count
> Move the data and the log file. Or, backup and restore. What you're
doing
> is quite similar roulette, and you've merely been on a lucky roll until
this
> spin.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Bryan old chap try this see if you get any joy and let me know :)
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
--
Br,
Mark Broadbent
mcdba , mcse+i
============="Bryan" <bryan.charlton@.api-wi.com> wrote in message
news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> After detaching a database and deleting the logfile, I use
> sp_attach_single_file_db to attach the database and get the following
error:
> Could not open new database 'Orders'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'F:\'Orders' may be
> incorrect.
> Is there any way to recover without the log file now?
>
>
>
>
>|||P45 time' Punt'
-- George Hester
__________________________________
"Mark Broadbent" <no-spam-please@.no-spam-please.com> wrote in message =news:etZkUVTZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> theoretically he should have been alright
> Inside SQL 2000 "One benefit of using the sp_detach_db procedure is =that SQL
> Server will know that the database was cleanly shut down, and the log =file
> does not have to be available to attach the database. SQL will build a =new
> log file for you. This can be a quick way to shrink a log file that =has
> become much larger than you would like, because the new log file that
> sp_attach_db creates for you will be the minimum size-less than 1 MB. =Note
> that this trick for shrinking the log will not work if the database =has more
> than one log file."
> > But you're right he should have been a bit more careful on a =production
> server. P45 time!
> > -- > > > Br,
> Mark Broadbent
> mcdba , mcse+i
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:Omp91KSZEHA.1048@.tk2msftngp13.phx.gbl...
> > > I have actually done this more times than I can count
> >
> > Move the data and the log file. Or, backup and restore. What =you're
> doing
> > is quite similar roulette, and you've merely been on a lucky roll =until
> this
> > spin.
> >
> > -- > > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >|||Are you sure you are providing the proper data file name?
F:\Orders ? with no extension...
If the filename or directory is inaccurate you will get the same message you
are now seeing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bryan" <bryan.charlton@.api-wi.com> wrote in message
news:%23IVTepRZEHA.3016@.tk2msftngp13.phx.gbl...
> After detaching a database and deleting the logfile, I use
> sp_attach_single_file_db to attach the database and get the following
error:
> Could not open new database 'Orders'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'F:\'Orders' may be
> incorrect.
> Is there any way to recover without the log file now?
>
>
>
>
>|||"P45 time" is a common UK expression meaning it is time to get a new job.
P45 is the number on the standard UK form that states your tax details that
an emploer is obliged to give you when you leave their employment!
Mike John
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:uxQ2fhUZEHA.3432@.TK2MSFTNGP10.phx.gbl...
P45 time' Punt'
--
George Hester
__________________________________
"Mark Broadbent" <no-spam-please@.no-spam-please.com> wrote in message
news:etZkUVTZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> theoretically he should have been alright
> Inside SQL 2000 "One benefit of using the sp_detach_db procedure is that
SQL
> Server will know that the database was cleanly shut down, and the log file
> does not have to be available to attach the database. SQL will build a new
> log file for you. This can be a quick way to shrink a log file that has
> become much larger than you would like, because the new log file that
> sp_attach_db creates for you will be the minimum size-less than 1 MB. Note
> that this trick for shrinking the log will not work if the database has
more
> than one log file."
> But you're right he should have been a bit more careful on a production
> server. P45 time!
> --
>
> Br,
> Mark Broadbent
> mcdba , mcse+i
> =============> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:Omp91KSZEHA.1048@.tk2msftngp13.phx.gbl...
> > > I have actually done this more times than I can count
> >
> > Move the data and the log file. Or, backup and restore. What you're
> doing
> > is quite similar roulette, and you've merely been on a lucky roll until
> this
> > spin.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
>

Wednesday, March 7, 2012

Housekeeping blobs in a DB.

Hi,

VS2005

SQl workgroup 2005

I have a DB where I store alot of Blobs, the blobs are tagged up with alot of id. My concern is deleting them automatically on a regular basis, too keep the DB size down. I will be deleting them based on based on age(date/time) and what category the blob is classed as.

- My concern is how to delete blobs sort of equally dispersed among the categories so no category ends up empty although it may have old pictures in it.

Any tips, links or code is much welcome.

(my app will be acessing the DB though ADO .NET 3.0 c++/cli)

Cheers,

Andreas

WHat about using DELETE TOP(50 PERCENT) From SomeTable Where DateisOld ?

That would not delete every record which is old and will leave some of them still on the disk/Database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--|||

Jens,

Hi, that was a good suggestion, I wasn′t aware of the TOP command. It sort of is useful maybe if its used with

a > 1000 to ensure there is atleast 1000 blobs in each category?

now I need to solve such a query

Any help is appriciated!

Cheers,

/Andreas

|||A quick sample for your asked query would be:

DELETE
Production.ProductProductPhoto
FROM Production.ProductPhoto P
INNER JOIN
(
SELECT ProductPhotoId,ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter
From Production.ProductPhoto
) SubQuery
ON P.ProductPhotoId = SubQuery.ProductPhotoId
WHERE SubQuery.Counter > 1000

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Thanks for your swift reply, will try what youre suggesting.

so far i′ve come up with a CTE solution that′s close but not totally there

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY Category

ORDER BY Age DESC) AS rownum, ID FROM Imageblob)

DELETE FROM tempdata WHERE rownum < 1000;

hmm can you use DELETE TOP within a CTE? i didn′t get it to work.

Cheers,

/Andreas

|||

Whats your error message ?

THis works for me:

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter, ProductPhotoId FROM Production.ProductPhoto)

DELETE FROM tempdata WHERE counter > 1000;

Remember that you want to do a >1000 instead of a <1000

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Jens,

Hi,

ah, the <1000 was a typo.

Error message? I don′t really get the hang of where to put the TOP cmd.

Cheers,

/Andreas.

|||YOu don′t need the TOP if you use the TVF.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Ah,

I was toying with the idea to use TOP and Percentage rather than a fixed number to delete.

Cheers,

/Andreas

|||

Ok, is the problem solved then ? You can either delete a percentage or leave a threshold of rows in the table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

I think so, ofc there are some more things I′ll try to add if I fail i′ll be back.

But thanks for your time and advice Jens!

Cheers

/Andreas

|||

Jens,

Ah, the phrase equal deletion is perhaps not accurate, I want to delete in the categories with most

rows. When i′ve created the CTE how can I from that deduce which Category in tempdata holds most

rows? Where do I fit a Count/Count_big?

And then delete from the category which has most rows and or delete more from the first, less from the second... etc

Code Snippet

count(age)

from Imageblob

Group by Category;

gives me rows per category, but with the CTE it gives me other number probably correct

based on the the tempdata is.

Cheers,

/Andreas

|||

The statement mentioned in your last post will elimenate the NULL values, therefore you probably have another count than the expected one of the CTE. For only deleting in the "biggest" category, you will have to query

Code Snippet

WITH tempdata AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter,
ProductPhotoId
FROM Production.ProductPhoto
)
DELETE tempdata
FROM tempdata F
INNER JOIN
(
SELECT TOP (1) COUNT(*),category FROM tempdata
GROUP BY category
ORDER BY COUNT(*)
) SubQueryForCount
ON SubQueryForCount.category = F.tempdata
WHERE counter > 1000;

Jens K. Suessmeyer

http://www.sqlserver2005.de

Monday, March 12, 2012

How bad is this??

How bad is this??

How bad is this??

Wednesday, March 7, 2012

Housekeeping blobs in a DB.

hot backup

Blog Archive

About Me