Remove ldf file

Hi all!
I have a SQL Server database with a secondary datafile (*.ndf). I would like to be able to delete that file and only use the *.mdf to store data. Of cource I also have a transaction log file.
Is it possible to move data from the ndf to the mdf and the delete the ndf'
Regards
/FredrikHi,
SQL Server spans data across all data files within a filegroup.So all files
fill will be used for all DMLs. to remove a file , you must first have the
data moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHEINKFILE command.
This will empty the file and mark it as unavailable. After that execute
REMOVE FILE in ALTER DATABASE command to drop the NDF file.
See DBCC SHRINKFILE and ALTER database in books online for syntax.
--
Thanks
Hari
MCDBA
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:5DCC35E5-43A0-45B7-A3F6-81353A62F09E@.microsoft.com...
> Hi all!
> I have a SQL Server database with a secondary datafile (*.ndf). I would
like to be able to delete that file and only use the *.mdf to store data. Of
cource I also have a transaction log file.
> Is it possible to move data from the ndf to the mdf and the delete the
ndf'
> Regards
> /Fredrik

Remove Backup History

I have done a database backup and store the backup file in a harddisk folder.
Now,
I want to remove the backup history log from the Restore Database dialog
window but I could not find the command to remove it.
exec msdb.dbo.sp_delete_backuphistory
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> I have done a database backup and store the backup file in a harddisk
folder.
> Now,
> I want to remove the backup history log from the Restore Database dialog
> window but I could not find the command to remove it.
|||Thanks a lot. It works.
"Hassan" wrote:

> exec msdb.dbo.sp_delete_backuphistory
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> folder.
>
>

Remove Backup History

I have done a database backup and store the backup file in a harddisk folder
.
Now,
I want to remove the backup history log from the Restore Database dialog
window but I could not find the command to remove it.exec msdb.dbo.sp_delete_backuphistory
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> I have done a database backup and store the backup file in a harddisk
folder.
> Now,
> I want to remove the backup history log from the Restore Database dialog
> window but I could not find the command to remove it.|||Thanks a lot. It works.
"Hassan" wrote:

> exec msdb.dbo.sp_delete_backuphistory
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> folder.
>
>

Remove Backup History

I have done a database backup and store the backup file in a harddisk folder.
Now,
I want to remove the backup history log from the Restore Database dialog
window but I could not find the command to remove it.exec msdb.dbo.sp_delete_backuphistory
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> I have done a database backup and store the backup file in a harddisk
folder.
> Now,
> I want to remove the backup history log from the Restore Database dialog
> window but I could not find the command to remove it.|||Thanks a lot. It works.
"Hassan" wrote:
> exec msdb.dbo.sp_delete_backuphistory
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:877ED168-5E2E-44CA-ABD8-9E16F83B9AA1@.microsoft.com...
> > I have done a database backup and store the backup file in a harddisk
> folder.
> > Now,
> > I want to remove the backup history log from the Restore Database dialog
> > window but I could not find the command to remove it.
>
>sql

Remote SQL Server Access

I'm using SQL Server to store session state. The Sql Server is on the local network but not on the web server. I use impersonation and use account credentials for the ASPNET worker process:

<identity impersonate="true" userName="domain\user" password="pass"/
I'm able to access all resources and execute all pages without any problem.

When an object is added to session state I recieve the following error:

"Cannot open database requested in login 'ASPState'. Login fails. Login failed for user 'domain\username. "

Here is my web.config entry for using sql server (with modified connection string of course):

<sessionState mode="SQLServer" cookieless="false" timeout="30" stateConnectionString="Data Source=xxx.xxx.x.xx,1433;Network Library=DBMSSOCN;Initial Catalog=ASPState;Integrated Security=SSPI" /
I've checked and given the user i'm impersonating the ability to do everything. I've granted access on all necessary files, added them to the DB and made the user a db_owner of the ASPState table. I can log in to a machine as the user, then access sql server using the windows authentication. I granted 'act as part of the operating system', 'access this computer from a network', 'log on as a service', 'log on as a batch job' on both the remote computer hosting sql server and the web server.

The strange thing is i installed the asp session state tables on my development machine, using the same configuration file to run everything, and was able to add an object to session state without any problem. But when i specify the remote server, i'm denied.

Anybody have an idea?Have you tried switched back from integrated and supplying the said user details in the connection string? Not suggesting that as a solution but just wondering if that works.

Remote Replication

Hi,

Now, I try doing remote replication between 2 pc(eg.pc A and B). Pc A as a server for publisher and distributor, and PC B is a normal pc to store data. I want replicate data from PC B to PC A. I used merge replication for this replication. I don't understanding something like:

1. is I need 2 servers for this replication? One server for publisher and distributor and others one server for subscriber?

2. What should I do if one server also can doing replication? What the step?

karencylIntroducing Replication Options
Options available with the types of replication allow you more replication solutions and greater flexibility and control in your applications. Replication options are:

Filtering published data

Publishing database objects

Publishing schema objects

Updatable subscriptions

Transforming published data

Alternate synchronization partners
Filtering Published Data
Filtering data during replication allows you to publish only the data or partitions of data that are needed at the Subscriber. You can filter data to create partitions that include only the columns and/or only the rows that you specify for replication.

With all types of replication, you can choose to copy and distribute complete tables, or data filtered horizontally or vertically with static filters. Merge replication is especially strong in filtering options, and you can use dynamic filters to customize the filter based on a property of the Subscriber receiving the data.

Filtering data horizontally allows you to publish only the data that is needed, partition data to different sites, avoid conflicts (because Subscribers will be viewing and updating different subsets of data), and manage publications based on user needs or applications.

Additionally, you have the option of employing user-defined functions in your static and dynamic filters and leveraging the power of customized functions.

Merge replication provides the added functionality of join filters and dynamic filters. Join filters enable you to extend filters created on one table to another. For example, if you are publishing customer data based on the state where the customer resides, you may want to extend that filter to the related orders and order details of the customers in a particular state. Dynamic filters allow you to create a merge publication and then filter data from the publishing table.. The filter value can be the user ID or login retrieved based on a Transact-SQL function, such as SUSER_SNAME() or HOSTNAME().

Publishing Database Objects
You can publish database objects including views, indexed views, user-defined functions, stored procedure definitions, and the execution of stored procedures. You can include data and database objects in the same publication or in different publications. Publishing database objects is available with all types of replication (snapshot replication, transactional replication, and merge replication).

Publishing Schema Objects
In addition to database objects, you can also specify if you want schema objects to be published such as declared referential integrity (primary key constraints, reference constraints, unique constraints), clustered indexes, nonclustered indexes, user triggers, extended properties, and collation. You can also change destination table owner names and data formats to optimize for SQL Server 2000 or heterogeneous Subscribers.

Updatable Subscriptions
Data at the Subscriber can be modified if you use merge replication or if you use snapshot replication or transactional replication with an updatable subscription option.

Updatable subscription options available with snapshot replication and transactional replication allow you to make changes to replicated data at the Subscriber and propagate those changes to the Publisher and to other Subscribers. Updatable subscription options include immediate updating, queued updating, and immediate updating with queued updating as a failover.

Immediate updating allows Subscribers to update data only if the Publisher will accept them immediately. If the changes are accepted at the Publisher, they are propagated to other Subscribers. The Subscriber must be continuously and reliably connected to the Publisher to make changes at the Subscriber.

Queued updating allows Subscribers to modify data and store those data modifications in a queue while disconnected from the Publisher for a period of time. When the Subscriber reconnects to the Publisher, the changes are propagated to the Publisher. If the Publisher accepts the changes, normal replication processes occur and the changes are propagated to other Subscribers from the Publisher. You can store data modifications in a SQL Server 2000 queue or use Microsoft Message Queuing.

Immediate updating with the queued updating option allows you to use immediate updating and switch to queued updating if a connection cannot be maintained between the Publisher and Subscribers. After switching to queued updating, reconnecting to the Publisher, and emptying the queue, you can switch back to immediate updating mode.

When using merge replication, data at the Subscriber is automatically updatable.

Transforming Published Data
With snapshot replication or transactional replication, you can leverage the transformation mapping and scripting capabilities of Data Transformation Services (DTS) when building a replication topology. Replication integrated with DTS allows you to customize and distribute data based on the requirements of individual Subscribers. For example, a Subscriber might need to have different table names, column names, or compatible data types.

By transforming published data, you can filter data and simulate dynamic partitions of data so that data from one snapshot or transactional publication can be distributed to Subscribers that require different partitions of data. With static partitions, you need to create and filter separate publications for each Subscriber based on the needs of the Subscriber.

Alternate Synchronization Partners
Subscribers to merge publications can synchronize with servers other than the Publisher at which the subscription originated. Synchronizing with alternate partners allows Subscribers to synchronize data even if the primary Publisher is unavailable. This feature is also useful when mobile Subscribers have access to a faster or more reliable network connection with an alternate Publisher.

See Also

Alternate Synchronization Partners

Filtering Published Data

Merge Replication or Updatable Subscriptions

Publishing Data and Database Objects

1988-2000 Microsoft Corporation. All Rights Reserved.

Remote queries

I nee to execute a proc on few servers and store the results on a
central database in a table. How can I do this without writing the DTS
packages?
I tried the Sybase way ( Server...sp_myproc) and it did not work. I
added the linked server before trying this. Any pointers'
ThanksIt 'should' work by using the Four part name, e.g., ServerName.Database.Sche
ma.Object.
For Example:
Execute MyServer.Northwind.dbo.CustOrderHist
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"BS" <minimyme@.yahoo.com> wrote in message news:1154381608.618328.125960@.s13g2000cwa.googleg
roups.com...
>I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
>
> I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
>
> Thanks
>|||Hi
You could do this in a single DTS and use a Dynamic properties task to
change source server, database and table information.
John
"BS" wrote:

> I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
> I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
> Thanks
>|||Here is the SQL that I'm trying to execute and the columns that the
proc returns and the table structute that I want the data to be
inserted into.
create table DBSpace (
ServerName nvarchar(20) null,
DBName nvarchar(20) null,
SegType nvarchar(20) null,
AllocatedSpace int null,
UsedSpace int null,
[Free%] int null )
insert into DBSpace
execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
If I try to execute this, I get the error as below
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
Remove the quotes from around
'WHP-MactiveTest.master.dbo.sp_db_space_used'
Roy Harvey
Beaon Falls, CT
On 1 Aug 2006 12:16:38 -0700, "BS" <minimyme@.yahoo.com> wrote:

>Here is the SQL that I'm trying to execute and the columns that the
>proc returns and the table structute that I want the data to be
>inserted into.
>create table DBSpace (
>ServerName nvarchar(20) null,
>DBName nvarchar(20) null,
>SegType nvarchar(20) null,
>AllocatedSpace int null,
>UsedSpace int null,
>[Free%] int null )
>
>insert into DBSpace
>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>If I try to execute this, I get the error as below
>Msg 102, Level 15, State 1, Line 2
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||Two problems.
1. No should NOT be Quotes around the object name
2. In order to use a server name that includes a dash [-] or instance na
me that includes a slash [\], you need to surround ONLY the server name
with square brackets.
E.g.,
EXECUTE [WHP-MactiveTest].master.dbo.sp_db_space_used
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"BS" <minimyme@.yahoo.com> wrote in message news:1154459798.626453.71360@.m73g2000cwd.googlegr
oups.com...
> Here is the SQL that I'm trying to execute and the columns that the
> proc returns and the table structute that I want the data to be
> inserted into.
>
> create table DBSpace (
> ServerName nvarchar(20) null,
> DBName nvarchar(20) null,
> SegType nvarchar(20) null,
> AllocatedSpace int null,
> UsedSpace int null,
> [Free%] int null )
>
>
> insert into DBSpace
> execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>
> If I try to execute this, I get the error as below
>
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
>

Remote queries

I nee to execute a proc on few servers and store the results on a
central database in a table. How can I do this without writing the DTS
packages?
I tried the Sybase way ( Server...sp_myproc) and it did not work. I
added the linked server before trying this. Any pointers'
ThanksThis is a multi-part message in MIME format.
--=_NextPart_000_0078_01C6B4B9.9CC3E0B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It 'should' work by using the Four part name, e.g., =ServerName.Database.Schema.Object.
For Example:
Execute MyServer.Northwind.dbo.CustOrderHist
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"BS" <minimyme@.yahoo.com> wrote in message =news:1154381608.618328.125960@.s13g2000cwa.googlegroups.com...
>I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
> > I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
> > Thanks
>
--=_NextPart_000_0078_01C6B4B9.9CC3E0B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

It 'should' work by using the Four part =name, e.g., ServerName.Database.Schema.Object.
For Example:
Execute MyServer.Northwind.dbo.CustOrderHist
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"BS" =wrote in message news:1154381608.618328.125960@.s13g2000cwa.googlegroups.com...>I =nee to execute a proc on few servers and store the results on a> central database =in a table. How can I do this without writing the DTS> =packages?> > I tried the Sybase way ( Server...sp_myproc) and it did not =work. I> added the linked server before trying this. Any =pointers'> > Thanks>

---

--=_NextPart_000_0078_01C6B4B9.9CC3E0B0--|||Hi
You could do this in a single DTS and use a Dynamic properties task to
change source server, database and table information.
John
"BS" wrote:
> I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
> I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
> Thanks
>|||Here is the SQL that I'm trying to execute and the columns that the
proc returns and the table structute that I want the data to be
inserted into.
create table DBSpace (
ServerName nvarchar(20) null,
DBName nvarchar(20) null,
SegType nvarchar(20) null,
AllocatedSpace int null,
UsedSpace int null,
[Free%] int null )
insert into DBSpace
execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
If I try to execute this, I get the error as below
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
Remove the quotes from around
'WHP-MactiveTest.master.dbo.sp_db_space_used'
Roy Harvey
Beaon Falls, CT
On 1 Aug 2006 12:16:38 -0700, "BS" <minimyme@.yahoo.com> wrote:
>Here is the SQL that I'm trying to execute and the columns that the
>proc returns and the table structute that I want the data to be
>inserted into.
>create table DBSpace (
>ServerName nvarchar(20) null,
>DBName nvarchar(20) null,
>SegType nvarchar(20) null,
>AllocatedSpace int null,
>UsedSpace int null,
>[Free%] int null )
>
>insert into DBSpace
>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>If I try to execute this, I get the error as below
>Msg 102, Level 15, State 1, Line 2
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||This is a multi-part message in MIME format.
--=_NextPart_000_03A4_01C6B567.F7543DF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Two problems.
1. No should NOT be Quotes around the object name
2. In order to use a server name that includes a dash [-] or instance =name that includes a slash [\], you need to surround ONLY the server =name with square brackets.
E.g.,
EXECUTE [WHP-MactiveTest].master.dbo.sp_db_space_used
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"BS" <minimyme@.yahoo.com> wrote in message =news:1154459798.626453.71360@.m73g2000cwd.googlegroups.com...
> Here is the SQL that I'm trying to execute and the columns that the
> proc returns and the table structute that I want the data to be
> inserted into.
> > create table DBSpace (
> ServerName nvarchar(20) null,
> DBName nvarchar(20) null,
> SegType nvarchar(20) null,
> AllocatedSpace int null,
> UsedSpace int null,
> [Free%] int null )
> > > insert into DBSpace
> execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
> > If I try to execute this, I get the error as below
> > Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
>
--=_NextPart_000_03A4_01C6B567.F7543DF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Two problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>1. No should NOT be Quotes around the =object name</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>2. In order to use a server name that =includes a dash [-] or instance name that includes a slash [\], you need to =surround ONLY the server name with square brackets.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>E.g.,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>EXECUTE [WHP-MactiveTest].master.dbo.sp_db_space_used</FONT></DIV>
<DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, =Ph.D.<BR>Westwood Consulting, Inc</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Most good judgment comes from =experience. <BR>Most experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>"BS" <</FONT><A href=3D"mailto:minimyme@.yahoo.com"><FONT face=3DArial size=3D2>minimyme@.yahoo.com</FONT></A><FONT face=3DArial size=3D2>> =wrote in message </FONT><A href=3D"news:1154459798.626453.71360@.m73g2000cwd.googlegroups.com"><FONT =
face=3DArial size=3D2>news:1154459798.626453.71360@.m73g2000cwd.googlegroups.com</FONT>=</A><FONT face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> =Here is the SQL that I'm trying to execute and the columns that the<BR>> proc returns =and the table structute that I want the data to be<BR>> inserted =into.<BR>> <BR>> create table DBSpace (<BR>> ServerName nvarchar(20) =null,<BR>> DBName nvarchar(20) null,<BR>> SegType nvarchar(20) null,<BR>> AllocatedSpace int null,<BR>> UsedSpace int null,<BR>> [Free%] int =null )<BR>> <BR>> <BR>> insert into DBSpace<BR>> execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'<BR>> <BR>> If I try =to execute this, I get the error as below<BR>> <BR>> Msg 102, Level =15, State 1, Line 2<BR>> Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.<BR>></FONT></BODY></HTM=L>
--=_NextPart_000_03A4_01C6B567.F7543DF0--